Probably more a persistence question (which is influx in this case)
If I’ve got values on and off for my alarm, or tv for example, I wouldn’t find trying to determing how long over the day that an item was in a state of on.
Ideally I’d prefer to do this by by using the persistence layer I’m already using to do this rather than keeping variables in openhab.
Has anyone tried this? Maybe pulled values from persistence and calculated that way, if not pulled a value direct from persistence via a query.
If your persistence stategy for the item is everyChange, and you looked at your raw data, with the times converted so that they were human readable, it would look something like this…
Time
State
Sun Aug 05 2018 21:48:58 GMT-0400 (EDT)
OFF
Sun Aug 05 2018 21:48:58 GMT-0400 (EDT)
ON
Sun Aug 05 2018 21:45:22 GMT-0400 (EDT)
ON
Sun Aug 05 2018 21:45:22 GMT-0400 (EDT)
OFF
Sun Aug 05 2018 21:29:59 GMT-0400 (EDT)
OFF
Sun Aug 05 2018 21:29:59 GMT-0400 (EDT)
ON
Sun Aug 05 2018 21:26:01 GMT-0400 (EDT)
ON
Sun Aug 05 2018 21:26:01 GMT-0400 (EDT)
OFF
Sun Aug 05 2018 21:11:58 GMT-0400 (EDT)
OFF
Sun Aug 05 2018 21:11:58 GMT-0400 (EDT)
ON
Sun Aug 05 2018 21:08:16 GMT-0400 (EDT)
ON
Now, I can’t think of any way to sum the differences of the ONs and OFFs through the current persistence extensions, so you’ll need to use the REST API to get the data, separate out the values, and add it up. Something like this…
val Integer dataPoints = Integer::parseInt(transform("JSONPATH", "$.datapoints", executeCommandLine("/bin/sh@@-c@@/usr/bin/curl -s http://localhost:8080/rest/persistence/items/DS_Kitchen_Motion",10000)))
val String data = executeCommandLine("/bin/sh@@-c@@/usr/bin/curl -s http://localhost:8080/rest/persistence/items/DS_Kitchen_Motion",10000)
val String firstValue = transform("JSONPATH", "$.data[" + (dataPoints - 1).toString + "].state", data)
var Number sumTimes = 0
for (var i = if (firstValue == "OFF") dataPoints - 1 else dataPoints - 3; i >= 2; i -= 4) {
sumTimes = sumTimes + Long::parseLong(transform("JSONPATH", "$.data[" + i.toString + "].time", data)) - Long::parseLong(transform("JSONPATH", "$.data[" + (i - 2).toString + "].time", data))
}
logDebug("Rules","Test: sumTimes=[{}], minutes=[{}]",sumTimes,(0.5 + (sumTimes / (1000 * 60))).intValue)
You will need to change the item in line 2 to one of your Switch items, and have the JSONPATH transform service installed. Then you could use the data in a rule or create an item to hold it.
I’m 95% positive that InfluxDB stores Switch Items as 1 for ON and 0 for OFF. If you set the strategy to everyMinute (and ONLY every minute) then you can use something like:
val onMinutes = MyItem.sumSince(now.minusHours(1))
will return the sum of the 1’s in the DB meaning the number of minutes over the past hour the Switch was ON.
It’s an alternative approach that might be simpler to implement but comes with obvious limitations.
But wouldn’t the persistence extensions still return states (ON/OFF)? If so, I’m not so sure sumSince would work for them, no matter what type of persistence service was being used. For JDBC-MariaDB, sumSince on a switch item always returns 0(!).
This used to work because in InfluxDB it stores the state as 0 and 1, not “ON” and “OFF”. sumSince just assumes the results are Numbers and adds them all up. I used to do this with rrd4j back when it supported Switches.
I’m pretty sure it doesn’t work with MariaDB, MySQL, or the other JDBC DBs because it doesn’t save the states as numbers. But OP indicates he is using InfluxDB so it should work.
Ahh right, yes I am storing 1 and 0 for on and off.
I was hoping to get (from the influx community), a magical query to do it using elapsed, so that I don’t have to radically change my persistence options to every minute, as I currently post on change and every hour.
But of course as usual openhab community actually gives me a few answers while influx I’m getting crickets.
If you don’t want to change your persistence strategy, then you need to use the approach outlined by Scott using the REST API and parse the results to do your calculation. It will be a bit more complicated but very doable.
There is no way to directly query any of the databases from OH Rules. You can use the REST API like Scott illustrated or you can use the persistence methods on the Item Class. This is necessary to make all the various DBs look the same from OH’s perspective.
