Duration of switch ON time in hour_minutes_seconds

I have a switch, sensor, motor, … - they have ON, OFF status. I would like a graph to display(calculate) the ON, OFF duration of the device.

If Iam choosing an interval on Grafana I would like to see how much time was ON and OFF that specific device. I would like the correct duration value not just approximation, becuse I have all the data in DB. Is there any method, aggregation, any tool what can I use for such calculation ?

Is this posibbile with Grafana ? Do you have a working example? If not is there any plugin for that ? Maybe I need to use different DB then InfluxDb ?
Thx for the response.

Hi,

I think you could use discrete pluging.

Yes, I know that plugin, I did some test but it looks like its not working as expected. The calculated values are not correct. Do you have some working example ? Thx

What is your table structure that you want to display? Perhaps you need to build the proper query.

The table structure is very simple, you can see above. I have timestamp and ON or timestamp and OFF.
Does not matter the power, just want a correct values. Do you have some sample, or example ?

Try this

A:

SELECT
  time as "time_on"
FROM
  'state'
WHERE ('entity_id' = 'entrance')
AND 'state' = 'on'
AND $__timeFilter
GROUP BY time
ORDER BY time ASC

B:

SELECT
  time as "time_off"
FROM
  'state'
WHERE ('entity_id' = 'entrance')
AND 'state' = 'off'
AND $__timeFilter
GROUP BY time
ORDER BY time ASC

Then you can get the time value of On and Off.

Perhaps this not what you need… :slight_smile:

Thx for the response.
Its not good for me, as I find out I do not have timestamp in series, Iam using InfluxDB.
The timestamp is there but I cannot use in in the query, I don’t know why.
Its show up when I list a value but I can not use it as: SELECT time FORM ‘state’

SELECT state, value, time FROM “state” WHERE (“entity_id” = ‘entrance’ AND “domain” = ‘binary_sensor’) AND time >= now() - 12h limit 2;

Finaly I used the above plugin ( Discrete plugin for Grafana )

My query: SELECT value FROM “state” WHERE (“entity_id” = ‘irrigation_motor’) AND $timeFilter

I did not checked if the durations are good, I checked the number of counts (count 1 and count 0) those are 99% good.

2 Likes

Hello. You could use group by clause to divide the time into 1 minute or 1 second slots and select the option to keep last value if there is no sample.
This is how I did it with Postgresql:

The you select to get the sum of the samples, for example for single stat is:

image

You will get the sum of 1’s values, in my case the total minutes the signal was on.

2 Likes

This tip guided me on My dashboard monitoring installation.

Thanks,

this is a great way to achieve it, I just spent literally hours searching for a solution. Learning influx db still.

As grafana has evolved there is howto accomplish it with the current version.

  1. The query:

image

The important part here is field(state) followed by last. Then the group by that has a fill(previous). The syntax is different;

SELECT last("state") FROM "state" WHERE ("entity_id" = 'heating_mode') AND $timeFilter GROUP BY time(1m) fill(previous)
  1. We will need to add transforms to get the data.

We have 2 essential transformations here. First we remove the time without states, this is in my case happens because the database is new. You might not need it or even wish to count these “empty” states. Then the important transformation is the group by states and the will count the number of rows of each states.

  1. In the field settings set the unit to minutes(m).

You can of course groupby seconds in which case the unit will need to be seconds.

1 Like