I have a query that return an output with the machine state (_value) and mapValue to classify whether uptime/downtime with a duration (events.duration) for each state transition.
This is working fine, but the issue is: i want to be able to aggregate the total uptime/downtime by hour (or day in future).
I can't simply do a sum as those records highlighted in yellow will not reflect the total duration for that hour. For example: record1 01:50:00 Uptime 20s record2 02:10:00 Downtime 30s
If i want to account the uptime (record1) of the total duration should be split between Hour1 and Hour2. See the sample image (Target Output)
Can you suggest a sample Flux query to achieve the Target Output or perhaps a better way to achieve my objective.
