Getting trends and evaluating on/off times?

Grafana InfluxDB
1

Hi there!

I just started with OpenHAB and started dumping all kinds of data into influxdb. I also successfully migrated all my existing stats, like firewall traffic, core switch traffic, server health, blah… from RRDTool to Influxdb/Grafana. Learned quite a bit in the process!
Now I’d need some hints for some stuff. There’s a few things I’d like to graph which I haven’t been successful yet.
One things are averages and trends. I’d like to have averages for my weather station temperature and humidity, kinda like a trend curve. When I try the moving average option in Grafana, I get an error about wrong number of parameters (3 instead of expected 2).
The other thing I’d love to do is evaluating on/off times. I dump the status of all (light)switches as well as the presence data for my phone into influxDB with every change or at least once an hour. From this data, I would like to figure out how much time in the selected interval a switch was on and how much time it was off. My first experiment was counting ones and zeros but because of data being written at every change or once an hour, I potentially have a lot of NULLs which mess up the result…

2

Are you on an older version of InfluxDB? Noticed the same problem on the play.grafana.org site which has an older version of InfluxDB. This works with InfluxDB 1.3 for me.

Is null the same as zero in your case? There is an option in Grafana (“Stacking & Null value” section on the Display tab) for treating nulls as zero.

(Also check out Continuous queries - might be useful for this).

Sounds like you want to group by 1h together with a max/min/last/first aggregate function.

3

Thanks for your reply! I think the NULLs can be either 0 or 1. Here is an example:

sensors-internal
sensors-internal1000×500 12.2 KB

What I want to find out would be e.g. how many minutes in the selected time frame I was home (1) and how many minutes not (0).

4

Did you try using a group by and a sum aggregation?