I am looking for a way to compute uptime for various devices while only receiving discrete on/off events.
As of now, a flow in node-red writes the on/off events into a measurment as a “state” field with integer values 1 (switched on) or 0 (switched off). Only state-changed events are logged. So this is a sparse serie.
I can show the state on Grafana using staircase mode. All good.
SELECT “state” FROM “operating” where (“name” = ‘front-light’) and (time >= ‘2020-10-07’) and (time < ‘2020-10-08’)
Now I would like to write a CQ to daily compute daily the uptime of the devices.
I originally though I could use INTEGRAL() but INTEGRAL() is using an interpolation method (BTW: it would be great that doc details which method is used : linear, Simpson’s …) which is great with continuous values but not for what I’m trying to achieve:
SELECT integral(“state”,1m) FROM “operating” where (“name” = ‘skt-pcvalou’) and (time >= ‘2020-10-07’) and (time < ‘2020-10-08’)
While expected uptime is 30 minutes, INTEGRAL() returns 15 minutes because of interpolating a triangle while consdering a rectangle would be better in this case.
I can change the design of the measurment if this can help, but I can’t change the fact that my real-world data are ondiscrete /off events, not continuous data.
I need to keep having the possibility to display on Grafana the on/off state as staircase line.
I’ve considered using node-red to repeadly write the last value every XX seconds but I would prefer to avoid that if possible.
Any advice will be appreciated