[closed][unsolved]Influxdb sumSince not working for switches

Hi,
i’m running openhab 2.5 M1 with influxdb persistance on a different server. Storing values work as I can query them via Chronograf or Grafana.

My problem is that I want to use the sumSince function for a switch item and I found out that there seems to be a problem digesting the results of influxdb.

The relevant part of the rule is
var eventCount = SchlafzimmerFensterRechtsStatus.sumSince(now.minusDays(10), "influxdb") logInfo( "Rules", "event count: " + eventCount.toString )

The log output is
2019-06-20 20:04:27.937 [INFO ] [eclipse.smarthome.model.script.Rules] - event count: 0

The query to influxdb is
ts=2019-06-20T18:01:16.741087Z lvl=info msg="Executing query" log_id=0G92UeUW000 service=query query="SELECT value FROM openhab_db.autogen.SchlafzimmerFensterRechtsStatus WHERE time > 1560189676s LIMIT 2147483647"

and the HTTP response is
"okhttp/2.4.0" 6647858e-9385-11e9-9978-0242ac150002 52077

So the query gets sent to influx db the 52k size response is returned to openhab, but openhab still thinks there were 0 events

A slightly modified query to influxdb returning the sum is
SELECT **sum(value)** FROM openhab_db.autogen.SchlafzimmerFensterRechtsStatus WHERE time > 1560189676s LIMIT 2147483647
and the result is 10986.00.

There are duplicate events, but the expected result in the log is definately > 0

Any idea why? Any help is appreciated.

BTW. As far as I understand it should be possible to delegate the aggregation to influxdb

But are they actually stored as Numbers as far as OH is concerned? I just did a query through the REST API of openHAB and I’m seeing Switches being stored as “ON” and “OFF” .

image

You can’t sum “ON” and “OFF”.

rrd4j used to store Switches as 1 and 0 but sometime around the 2.0 release time frame it dropped support for Switches.

It appears that Grafana is smart enough to convert the “ON” and “OFF” to 1 and 0. Or the value being stored is in fact stored as 1 and 0 but the OH Persistence Engine converts those to “ON” and “OFF” so they can be used to update the Switch.

Perhaps but that isn’t the case for all databases that OH supports. The Persistence engine provides a normalized interface for all the databases and consequently it implements the aggregation to give a more consistent result. Otherwise we would need to know the ins and outs of each and every database in our Rules and elsewhere.

I’m pretty sure that InfluxDB has a REST API. You could make the query through that and let InfluxDB do the aggregation.

Thanks for the explanation. Actually this seems weird to me as the values are stored as 0 and 1.

This is the partly result of a request against the InfluxDB REST API
,[1561011240084,1],[1561011300113,1],[1561011360145,1],[1561011420171,1],[1561011480205,1],[1561011540257,1],[1561011600309,1],[1561011660363,1],[1561011720409,1],[1561011780449,1],[1561011840490,1],[1561011900536,1],[1561011960577,1],[1561012020614,1],[1561012080669,1],[1561012140714,1],[1561012200761,1]

Quering the same values from the OpenHAB REST API provides this result

   {
      "name": "SchlafzimmerFensterRechtsStatus",
      "datapoints": "2883",
      "data": [
        {
          "time": 1561011240084,
          "state": "OPEN"
        },
        {
          "time": 1561011300113,
          "state": "OPEN"
        },
        {
          "time": 1561011300113,
          "state": "OPEN"
        },

So OpenHAB would have the data to do it, but prefers to translate it before doing the aggregation.

Ok. Now I know the reason, but the way of the implementation limits itself.

Thanks @rlkoshak for the insight and the hint to look at the right place

Hej dolittle! Did you find a workaround for this problem?

Yes. I just use a HTTP Request inside the rule instead of sumSince and circumvent openhab completely in this case where “days” is another variable containing the period in days to look at.

val eventCount = transform(“JSONPATH”,"$…values[0].[1]",sendHttpGetRequest(“http://(influxdbhost):8086/query?db=openhab&q=SELECT%20sum(value)%20FROM%20openhab_db.autogen.”+triggeringItem.name+"%20WHERE%20time%20%3E%20now()-"+days+“d”))

Works pretty well.
Hope that helps.