Counting number of values of an item in influxDB?

Add-ons Persistence Services
1

Hi,

I would like to know how many times a light switch has been turned on of off.
All the changes are stored in influxDB and I can see them when using influxDBstudio.

How can I access this from openHab using one of the persistence extensions ?

-ben

2

You cannot query the InfluxDB directly. There are a set of methods on the Items that can get you part of the way there. For example

val numberOns = MyLight.sumSince(now.minusHours(1))

Will give the total number of times the value 1 (i.e. ON) appears in the database for the past hour. What that means though depends on how you have configured your persistence. For example, if you are saving the value every minute then it would be the number of minutes over the past hour that the Light was ON. But if you store every update it means that the light was updated with ON that many times over the past hour which may be meaningless.

There is no way to count the zeros (i.e. OFFs).

Ultimately you will probably have to write rules to keep track of the count that way.

1 Like
3

The persistence extensions don’t provide a way to do this, but you can query the persistence data through the REST API. I rely on this for…

You could do something like this to get the number of times the switch switched…

val String persistenceResult = executeCommandLine("/bin/sh@@-c@@/usr/bin/curl -s http://localhost:8080/rest/persistence/items/Virtual_Switch_1?starttime=" + now.minusDays(5).toString + "&endtime=" + now.toString,10000)
logDebug("Rules","Test1: persistenceResult=[{}]",persistenceResult)
val Integer numSwitches = Integer::parseInt(transform("JSONPATH", "$.datapoints", persistenceResult)) / 2
logDebug("Rules","Test1: numSwitches=[{}]",numSwitches)

You would need the JSONPATH transformation service instaled for this. You may wonder why I’m dividing by two… for items of OnOffType, the REST API will add in an additional state for each change to improve charting. If you look at the data, before every ON you’ll see an extra OFF, and vice-versa.

And as @rlkoshak pointed out, this would only work correctly if you are persistence strategy is everyChange.

4

Thanks for the suggestions, I’ll have a look and see what is the easiest way to implement!

-ben

5

another option if you use Node-Red