Duration of switch ON time in hour_minutes_seconds

[au190]

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.

[danielgonzalez]

Hi,

I think you could use discrete pluging.

Discrete plugin for Grafana

[au190]

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

[fadjar340]

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

[au190]

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 ?

[fadjar340]

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…

[au190]

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;

[au190]

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.

[justain]

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:

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

[nigerninja]

This tip guided me on My dashboard monitoring installation.

[oliverde8]

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:

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)

2. 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.

3. 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.

[daling]

Beautiful! I just stumbled upon your answer and it works like a bomb.

Have you figured out a way to display the results in a visualization? Such a stacked bar chart, gauge or pie chart perhaps?

Typical (simplest) use case: Showing a pie chart of % on vs off for a heating element

Thanks

[daling]

And obviously found the answer moments later. Changing to a graph from table format you need to ensure the Value options > Show = All values and NOT calculate.

[grant2]

In case anyone wants to do this same thing in Flux, there are a few ways that I know of:

If your machine or switch is always 0, 1, 0, 1, etc. then something like this:

from(bucket: "RetroEncabulator")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r["_measurement"] == "BinaryData")
  |> filter(fn: (r) => r["_field"] == "value")
  |> elapsed(unit: 1s)
  |> map(fn: (r) => ({ r with elapsedFloat: float(v: r.elapsed)/3600.0 })) // convert seconds to hours
  |> filter(fn: (r) => r["_value"] == 0) // add a filter statement after the elapsed function to filter out only those whose _value = 0. This is because the “active” time (when the state 1 goes to state 0) is captured in those records where _value = 0. If you wanted to know the time when the phone is NOT active, you would set the filter where _value = 1.
  |> aggregateWindow(every: 1d, fn: sum, column:"elapsedFloat")
  |> yield(name: "sum")

If your machine or switch generates consecutive On or Off values (such as 1, 1, 0, 1, 0, 0, 1, 1, etc.), then something like this:

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

and if one wants a bar chart of hours of “on” time per day, something like this: