Graphing Time in a State per Day

Grafana Time Series Panel
1

Hi,

I try to build a Graph that displays how long my Heating System is active in minutes.
This is a private project with my Raspberry PI 3

my data looks like this.

Timestamp               | current_stat
2017-05-01 00:00        | 0
2017-05-01 00:05        | 0
2017-05-01 00:10        | 1
2017-05-01 00:15        | 1
2017-05-01 00:20        | 1
2017-05-01 00:25        | 0
2017-05-01 00:23        | 0
...
2017-05-02 00:00        | 0
2017-05-02 00:05        | 0
2017-05-02 00:10        | 1
2017-05-02 00:15        | 0
2017-05-02 00:20        | 1
2017-05-02 00:25        | 0
2017-05-02 00:23        | 0

PS: Values could be missing the result should stay the same if the data
2017-05-01 00:15 | 1
would be missing.
The Graph should show the time diff summed per day

So like to have a graph with:

(15min)
  |    (10 min)
  |       |
  |       |
05/01    05/02   ...

Database is a InfluxDB.

It would be nice if someone could explain me how to get such a result. The SQL would be great and the options I have to set.

Current I have:

FROM rp_raw stat_therme WHERE service=request and metric=current_stat
SELECT field(value) ???
GROUP BY time(1d)
2

Have you tried using the sum aggregation?

Click the plus button:

Choose the aggregation from the aggregation sub menu:

3

Hi,

I tried it today, but then i only get the sum of value “on”, but I need the time as Bargraph.

I’d like to know how long the Heating System is on. Thats the Problem. If i use sum(),
I’ll get how often the Heating System is requested per day but not how long it is running.

4

I found a elapsed function in Influx, but I don’t get the wished data. :frowning:

5

Reread your question a couple of times and think I understand now.

You know that you are collecting your data every 5 minutes so the sum per day multiplied by 5 gives you the number of minutes.

The problem is the nulls. If there are gaps in the data then you need to fill them. InfluxDB has a fill function which takes a previous argument.

6

Maybe not exactly what you want, but have a look at grafana plugin “natel-discrete-panel”.
I find it useful for states change visualization in my case.

7

Did you ever get this resolved?
I’m trying to do the exact same thing. I am using the “natel-discrete-panel” and if I show yesterday I get yesterday value but it always rounds it up to the nearest hour.
I’d really like to build a gauge that starts with say 250 Gallons and create a query that gets the previous days runtime (burner burns 1.2 gallons an hour) does the math and subtracts the calculated gallons from the 250 and create an alarm when it gets to say 25 gallons. Ex. Previous days runtime 5.3 hours * 1.2 = 6.3 gallons. Day one 6.3 gallons subtracted from gauge. 250 - 6.3 = 243.7 Essentially an oil tank gauge. Hope someone could help.

8

Should be possible - InfluxDB has math functions: https://docs.influxdata.com/influxdb/v1.5/query_language/math_operators/

9

Yeah, I agree it should be possible but I don’t have the skill to pull it off and was looking for some help.
Database is hasshomeauto measurement is named burner-on-off and is fieldtype boolean.
Hope someone could help.