I have an InfluxDB measurement table, which stores the state of a system over time. Each data point has a tag that represents the state and a duration indicating the time it was in that state.
For example: | Time | State | Duration| | ---------------------- | -------------- | ------- | | 2023-05-08 14:59:30 | idle | 40 | | 2023-05-08 15:00:10 | working | 10 | | 2023-05-08 15:00:20 | idle | 7200 | | 2023-05-08 17:00:20 | working | 10 | | ... | ... | ... |
I want to calculate how much time was spent in each state for a given timewindow (eg an hour/day/month). This to make a bar chart that shows the state distribution over time.
For instance grouped per hour: | Time | State | Duration | | --------------------- | -------------- | -------------- | | 2023-05-08 14:00:00 | idle | 30 | | 2023-05-08 15:00:00 | idle | 3590 (10 + 3580)| | 2023-05-08 15:00:00 | working | 10 | | 2023-05-08 16:00:00 | idle | 3600 | | 2023-05-08 17:00:00 | idle | 20 | | 2023-05-08 17:00:00 | working | 10 |
Simply using GROUP BY "State", time(1h) won't give the correct results.
(eg: this would return for the idle state a value of 7200s for 2023-05-08 15:00:00 - which is wrong).
What would be the right approach to deal with this? I assume this is a common scenario?
Is this possible with InfluxQL or only in Flux?
