Visualize when was my appliance ON and OFF during 24 hrs a day Y axis and days in X axis

Grafana Dashboards
1

Dear board

I’ve a basic requirement and here is my expected visualization.

i’ve TV and Sound system connected to HA, ON and OFF events/logs are saved in influx(Grafana) where i can see in bar graphs using sum function and 24h time interval how many hours they are ON per day. what i need is following

24h 0 0 0 0 0 0
18h 0 0 0 0 0 0
12h 0 1 1 0 0 1
06h 1 1 1 1 1 0
00h 0 0 1 1 1 0
day-1 2 3 4 5 6

so, if we see from above we can easily understand my maximum on interval and time
per day for entire month or year based on time range. (0s has to display as blank and 1s are to be displayed as visual bars)
How actually to plot this kind of chart?

can someone support?

thank you in advance.

2

Welcome

Are you using influxql or flux query language?

3

Hello,

I’m using Grafana in “Home assistant server”

  1. where Grafana will take data from Influxdb
  2. All my visualizations are from Grafana
  3. Here is one of the example of query
SELECT sum("mean") /60 FROM (SELECT mean("value") FROM "state" WHERE ("entity_id" = 'lg_sound_system') 
AND $timeFilter GROUP BY time(1m) fill(previous)) 
WHERE $timeFilter GROUP BY time(24h) fill(null)
  1. I’ve added a dashboard from Grafana
  2. in Panel options there is something called “Query” where it shows InfluxDB
  3. Even in queries it says influxDB

so not sure to rightly answer.

let me know if you need something more.

Thank you.

4

The query seems Grafana not influx. Because when i copy the same in Influxdb it throws an error. Do Grafana dashboard has this functionality?