How to generate a heatmap with on/off state

Grafana InfluxDB
1

Hi! I have a dataset like this:

| TIME | state |
| 2022-02-10 01:01:01 | on |
| 2022-02-10 01:02:01 | on |
| 2022-02-10 01:04:01 | off |

I would like to create a heatmap which visualizes what percentage of the day my device was “on” or “off” (maybe via color … 100% “on” would be green and 100% “off” would be red).

I imagine someone must have done something similar before but I guess I am using the wrong search terms. Can you give me a few pointers how to achieve that?

(I am using the latest Grafana and Influx DB 1.8)

Thank you!

2

Hello :wave: and welcome to the forum, @larsxschneider

I don’t know how you are calculating 100% on or off. Most people use the Stat panel for that and then visualize the last value. You could also try exploring the State Timeline panel or Status History panel:

3

I agree with @mattabrams that a Stat panel would work better for this, but in case you want to see what a Status history panel application looks like, this is ours showing the open/close status of a valve.

image
image2285×433 59.6 KB

1 Like
4

Thank you for the hints @mattabrams and @grant2 ! I made it work with a State timeline panel. and the following Flux query:

from(bucket: "home_assistant/autogen")
  |> range(start: v.timeRangeStart, stop:v.timeRangeStop)
  |> filter(fn: (r) =>
    r._measurement == "something" and
    r._field == "state")
  |> fill(usePrevious: true)

I get a visualization simular to the one that @grant2 posted. However, my “off” state is pretty seldom and short. Therefore, I wonder if there is a way to configure the Stat Panel so that it shows one bar per day. The color of the bar per day should indicate the percentage on “on” time.

Do you have another hint for me how to achieve that? Maybe with Transforms?