Hi
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.
Hi, I have implemented state duration but in my case it is not working as expected.
The database has only one line recorded in on and then it will record the off when it is happening.
stateDuration is just calculating the time based on “on” value repeating. It is not working for that.
I think that we are all looking at the time difference between the time of “on” value to “off” value.
on my test, if i am using this :
|> stateDuration(fn: (r) => r._value == “on”)
The resut is going to be 0 when “on” status.
Any idea on how to get “off” time minus “on” time ?
This type of question has come up a lot of over the months / years, and I think sometimes each use case is a bit different. Do any of these solutions help?
I am not aware of any way to do this in SQL. So far, when trying to rewrite my Flux queries in SQL, I have been able to do a lot of the same general queries for time series, but computing elapsed time a value is ON or OFF has eluded me. Anxious to hear what @Anaisdg or @Jay_Clifford may be able to offer up here.
