Summarize stateduration to have total "on" hours per day

InfluxDB 2
, , ,
1

0

i would like to write a query in flux that return the total “on” hours in each day.

from(bucket: "hass")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r["_measurement"] == "binary_sensor.slave_ch_active_boiler_caldaia_ot")
  |> filter(fn: (r) => r["_field"] == "state")
  |> filter(fn: (r) => r["domain"] == "binary_sensor")
  |> filter(fn: (r) => r["entity_id"] == "slave_ch_active_boiler_caldaia_ot")
  |> stateDuration(fn: (r) => r._value == "on", column: "state_duration", unit: 1s)

i found the stateDuration function that return the on time for each period, but i’ve no idea how to summarize that value in days window because stateduration return several values while “on” and i shoud sum only the last value of each series:

enter image description here

does anybody know hot to realize that?

2

HI @Lorenzo_Mao and welcome to the InfluxDB forum.

Instead of the stateDuration function, use elapsed(). Have a look at this post. You should be able to modify for your purposes.

3

hello @grant1,
i checked that post but i think is not fitting in my case.
the suggested approach work only if there are no subsequent “on” record.
in my case there myght be several “on” records before a “off” one.
and several “off” before the next “on”

4

Hi @Lorenzo_Mao

Yeah, you’re right, the elapsed() example that I linked to works only if the switch/state alternates from 0 to 1 to 0 to 1, etc. (which is really the way most machines work, but I digress).

I think the events.duration() function will do the trick. See below and report back how it works for you.

import "contrib/tomhollingworth/events"
from(bucket: "hass")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r["_measurement"] == "binary_sensor.slave_ch_active_boiler_caldaia_ot")
  |> filter(fn: (r) => r["_field"] == "state")
  |> filter(fn: (r) => r["domain"] == "binary_sensor")
  |> filter(fn: (r) => r["entity_id"] == "slave_ch_active_boiler_caldaia_ot") 
  |> events.duration(
    unit: 1s,
    columnName: "duration",
    timeColumn: "_time",
    stopColumn: "_stop",
    stop: 2020-01-01T00:00:00Z
)
  |> map(fn: (r) => ({ r with durationFloat: float(v: r.duration)/3600.0 }))
  |> filter(fn: (r) => r["_value"] == "on")
  |> aggregateWindow(every: 1d, fn: sum, column:"durationFloat")
  |> yield(name: "sum")
5

Hello @grant1 ,
tried this approach also, but _start and _stop are equal in my records:

table	_start	_stop	_time	_value	_field	_measurement	domain	entity_id
0	2023-02-18T13:53:46.023587434Z	2023-02-25T13:53:46.023587434Z	2023-02-18T15:41:59.670103Z	off	state	binary_sensor.slave_ch_active_boiler_caldaia_ot	binary_sensor	slave_ch_active_boiler_caldaia_ot
0	2023-02-18T13:53:46.023587434Z	2023-02-25T13:53:46.023587434Z	2023-02-18T15:46:29.137909Z	off	state	binary_sensor.slave_ch_active_boiler_caldaia_ot	binary_sensor	slave_ch_active_boiler_caldaia_ot
0	2023-02-18T13:53:46.023587434Z	2023-02-25T13:53:46.023587434Z	2023-02-18T19:10:25.381886Z	off	state	binary_sensor.slave_ch_active_boiler_caldaia_ot	binary_sensor	slave_ch_active_boiler_caldaia_ot
0	2023-02-18T13:53:46.023587434Z	2023-02-25T13:53:46.023587434Z	2023-02-18T19:10:43.539707Z	off	state	binary_sensor.slave_ch_active_boiler_caldaia_ot	binary_sensor	slave_ch_active_boiler_caldaia_ot
0	2023-02-18T13:53:46.023587434Z	2023-02-25T13:53:46.023587434Z	2023-02-19T05:53:54.467277Z	on	state	binary_sensor.slave_ch_active_boiler_caldaia_ot	binary_sensor	slave_ch_active_boiler_caldaia_ot
0	2023-02-18T13:53:46.023587434Z	2023-02-25T13:53:46.023587434Z	2023-02-19T07:39:08.229583Z	on	state	binary_sensor.slave_ch_active_boiler_caldaia_ot	binary_sensor	slave_ch_active_boiler_caldaia_ot
0	2023-02-18T13:53:46.023587434Z	2023-02-25T13:53:46.023587434Z	2023-02-19T07:46:03.665023Z	on	state	binary_sensor.slave_ch_active_boiler_caldaia_ot	binary_sensor	slave_ch_active_boiler_caldaia_ot
0	2023-02-18T13:53:46.023587434Z	2023-02-25T13:53:46.023587434Z	2023-02-19T07:50:53.069487Z	on	state	binary_sensor.slave_ch_active_boiler_caldaia_ot	binary_sensor	slave_ch_active_boiler_caldaia_ot
0	2023-02-18T13:53:46.023587434Z	2023-02-25T13:53:46.023587434Z	2023-02-19T07:56:15.068724Z	on	state	binary_sensor.slave_ch_active_boiler_caldaia_ot	binary_sensor	slave_ch_active_boiler_caldaia_ot
0	2023-02-18T13:53:46.023587434Z	2023-02-25T13:53:46.023587434Z	2023-02-19T08:02:40.055467Z	on	state	binary_sensor.slave_ch_active_boiler_caldaia_ot	binary_sensor	slave_ch_active_boiler_caldaia_ot
0	2023-02-18T13:53:46.023587434Z	2023-02-25T13:53:46.023587434Z	2023-02-19T08:41:03.275994Z	on	state	binary_sensor.slave_ch_active_boiler_caldaia_ot	binary_sensor	slave_ch_active_boiler_caldaia_ot
0	2023-02-18T13:53:46.023587434Z	2023-02-25T13:53:46.023587434Z	2023-02-19T10:03:46.085434Z	off	state	binary_sensor.slave_ch_active_boiler_caldaia_ot	binary_sensor	slave_ch_active_boiler_caldaia_ot
0	2023-02-18T13:53:46.023587434Z	2023-02-25T13:53:46.023587434Z	2023-02-19T11:14:52.618209Z	off	state	binary_sensor.slave_ch_active_boiler_caldaia_ot	binary_sensor	slave_ch_active_boiler_caldaia_ot
0	2023-02-18T13:53:46.023587434Z	2023-02-25T13:53:46.023587434Z	2023-02-19T11:18:27.755729Z	off	state	binary_sensor.slave_ch_active_boiler_caldaia_ot	binary_sensor	slave_ch_active_boiler_caldaia_ot
0	2023-02-18T13:53:46.023587434Z	2023-02-25T13:53:46.023587434Z	2023-02-19T15:18:12.806762Z	off	state	binary_sensor.slave_ch_active_boiler_caldaia_ot	binary_sensor	slave_ch_active_boiler_caldaia_ot
0	2023-02-18T13:53:46.023587434Z	2023-02-25T13:53:46.023587434Z	2023-02-19T17:31:26.479208Z	off	state	binary_sensor.slave_ch_active_boiler_caldaia_ot	binary_sensor	slave_ch_active_boiler_caldaia_ot

so for some reason i cannot trust this columns…
Lorenzo

6

Do you have a column called “duration”?

7

I made it

import "influxdata/influxdb/monitor"
from(bucket: "hass")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r["_measurement"] == "binary_sensor.slave_ch_active_boiler_caldaia_ot")
  |> filter(fn: (r) => r["_field"] == "state")
  |> map(fn: (r) => ({ r with _level: r._value}))
  |> elapsed()
  |> monitor.stateChanges(fromLevel: "on", toLevel: "off")
  |> map(fn: (r) => ({ r with elapsedFloat: float(v: r.elapsed)/3600.0 }))
  |> aggregateWindow(every: 1d, fn: sum, column:"elapsedFloat", createEmpty: false)

monitor.stateChanges in combination with elapsed made the trick!

thanks for the suggestions!
Lorenzo

8

@Lorenzo_Mao Great job and thanks for reporting back (I am surprised at how many people ask strangers for help, then never report back if anything worked).

My experience with Flux so far is that there are usually many ways to achieve the result. Glad you found a solution.

9

Thanks for the help.

Thanks for the help.