My home automation (home assistant) provides the current state of my heating as either "idle", "heating" or "hot water". I am putting all that data into the InfluxDB which the timestamp of the state change and the new state. What I want is: Put a pie chart on my grafana dashboard das shows me, how long the heating system was in which state for one day or one month etc. What I am missing is a good way, how to transform the data from the query to make it usable as input for the pie chart. (I am still rather new to Grafana)
Can anybody please point me in the right direction or give me a working example?
My query currently looks like this:
SELECT "value"
FROM "state"
WHERE ("entity_id"::tag = 'heating_status') AND $timeFilter
Which returns
| timestamp | state |
|---|---|
| 2024-02-20T01:00:00 | heating |
| 2024-02-20T01:30:00 | idle |
| 2024-02-20T07:00:00 | warm water |
| 2024-02-20T07:20:00 | idle |
How can I transform it to return something like
| state | duration |
|---|---|
| idle | 21:30 |
| heating | 02:00 |
| war water | 00:30 |
(i know that the numbers are not matching between those tables ;))