Grafana Bar Chart for Device State

[reclusivemonkey]

I’m trying to create a bar chart which shows the duration of the time my phone shows “automotve” activity so I can view my commute times. I can make a query in grafana and display the time and state in a table very easily. However converting this into a bar chart seems completely beyond me. In order to do this type of thing do you need to transform the data using manual edited queries? I’ve tried unleashing ChatGPT on it and looking at lots of other examples but just don’t seem to be able to make any progress. If this would be easier to do in InfluxDB, I’m happy to use that instead, but I’ve had less luck there than in Grafana. I thought the “Transform data” GUI might help but I can’t see anything in there that will do what I want to do.

Here’s the current query;

SELECT distinct("state") FROM "state" WHERE ("entity_id"::tag = 'slim_charles_activity' AND "state" = 'Automotive') AND $timeFilter GROUP BY time($interval) fill(null)

[tom_l]

Use the history stats sensor in home assistant to measure the time the sensor state is “automotive” per day:

History stats - Home Assistant

It should then be trivial to plot this new sensor in Grafana:

[reclusivemonkey]

Hi Tom,

Thanks for the reply. This worked perfectly, just what I needed. It’s a really useful technique to know about, I can see this being really valuable.