Dear all,
i’am searching for a long time but I can’t find the right solution.
I have a switch control the boiler. switch.boiler
I’am searching to find the right query to know how long (in second or minutes) in one day, the boiler is in on position.
Tell us what data you are storing in your database.
For example:
are you storing the state (“on” or “off”) each second / minute / hour /
whatever
are you storing the state changes (“on->off” or “off->on”) whenever the
change occurs
something else?
Antony.
In the database I’am storing the state changes.
The switch is in hassio and write the change of the state in influxdb.
No one can help me with this problem?
How about something like:
For the time period of interest, add up all the Off times, add up all the On
times, and subtract the sum of the On from the sum of the Off.
If the answer is negative, add the duration of the time period.
I think the result will be the duration of time the state was On.
Example:
During one day (24 hours), suppose the state changes are:
08:00 On
09:30 Off
13:15 On
19:45 Off
Total on time = 1:30 + 6:30 = 8:00
Sum of Off times = 09:30 + 19:45 = 29:15
Sum of On times = 08:00 + 13:15 = 21:15
Sum(Off) - Sum(On) = 29:15 - 21:15 = 8:00
If there was no On state change at 08:00 (so it had already been on since
midnight):
Total on time = 9:30 + 6:30 = 16:00
Sum of Off times = 29:15
Sum of On times = 13:15
Sum(Off) - Sum(On) = 29:15 - 13:15 = 16:00
Alternatively, if the 08:00 On time was still there, but it didn’t turn off at
19:45 and instead remained on until midnight:
Total on time = 1:30 + 10:45 = 12:15
Sum of Off times = 09:30
Sum of On times = 21:15
Sum(Off) - Sum(On) = 09:30 - 21:15 = -11:45
This is negative, so add the duration (24 hours) = 12:15
Regards,
Antony.
I try with this and give me a graphic state of on state, but I need a number in minutes that sum all the on time. It’s possible to do?
SELECT mean(“value”) FROM “switch.caldaia” WHERE timeFilter GROUP BY time(__interval) fill(previous)
You will have to do some calculations on the actual timestamps, since that is
all the information you have from collecting “state changes” rather than
“current state”.
Also, I don’t know whether this scenario is plausible in your situation, but I
think that if the switch remains on continuously for 24 hours, or off
continuously for 24 hours, you have no way to know which it was.
Antony.
Ok so if I understand correctly, is not possible do a query that gives as result the time of certain state. I have to do the count manually, summing the time when the switch is on.
Hello @mr-varga,
You can use kapacitor to monitor state changes and state durations in 1.x. In 2.x you can use tasks. This thread and user might have some useful resources for you? Looking for a special solution for total uptime and counting on/off switching - #2 by Anaisdg
I appreciate your help but I think this is too much for me… 
