(how to) Computing uptime from on/off events

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.

Example data:

SELECT “state” FROM “operating” where (“name” = ‘front-light’) and (time >= ‘2020-10-07’) and (time < ‘2020-10-08’)
name: operating
time state
---- -----
2020-10-07T18:19:37.562555865Z 1
2020-10-07T18:50:09.799661971Z 0

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’)
name: operating
time integral
---- --------
2020-10-07T00:00:00Z 15.268642550883333

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

Hello @Barbudor,
You can easily do this with 2.x and Flux and Tasks.

With Flux you can specify the integral interpolation:

And you can also calculate the stateDuration time with:

Is upgrading to 2.0 an option for you? Otherwise I think you might have to use Kapacitor.

Hi @Anaisdg

I’m just starting with influxdb so that’s probbaly too big a step for now :wink:
But may be a good opportunity to learn Kapacitor

Many thanks.


Kapacitor is much harder to learn and there isn’t a lot of OSS support around it! I recommend using 2.x. It’s a lot easier.