How to display hourly power consumption?

Configuration
,
1

I’d like to somehow display the average power consumption for each hour of the day over a longer period of time.

With SQL this would be easily done with a GROUP BY -query like this:

SELECT AVG(state), HOUR(FROM_UNIXTIME(last_updated_ts)) FROM hass.states WHERE entity_id = 'sensor.power_total' GROUP BY HOUR(FROM_UNIXTIME(last_updated_ts))

This returns the average power consumption for each hour of the day, which is exactly the data I want, but I haven’t been able to find a card or integration that would visualize this data for me. Apex-charts seemed like the most potential option, but it only supports a timeline based chart.

Statistics-graph card is also not an option, since it doesn’t allow grouping the data by hour.

1 Like
2

Custom:mini-graph can aggregate data like that Here’s a link to someone displaying solar production on a series bar chart:

3

Thanks! I gave it a try but couldn’t really get it to display what I want.

This is the closest I got to:

image
image998×412 35.5 KB

but this behaves pretty much the same way as apex-chart did and only shows the consumption of the most recent day. If I add more hours to the hours_to_show, it just adds more datapoints to the chart instead of grouping by the hour.

The SQL query example returns data in this format:

state, hour
2324, 00
1823, 01
1910, 02
1501, 03
...
889, 22
1001, 23

which groups by the hour-value of the timestamp (ignoring the date), while these cards seem to group by hour and date.

At this point I’d be happy to even be able to display the raw data from the query in some table format if I can’t get any fancy charts. :grinning_face_with_smiling_eyes:

4

I managed to get this working by creating a SQL sensor that returns the average consumption in a single row (separated by commas). Then I parsed the value in an apex-chart.

Now I have a fancy chart that shows me during which hours I use the most energy and which hours are the most expensive in average.
image

SQL query:

SELECT AVG(state) as total_average, GROUP_CONCAT(state ORDER BY hour) as hourly_averages
FROM (
    SELECT ROUND(AVG(state), 0) as state, HOUR(FROM_UNIXTIME(last_updated_ts)) as hour
    FROM hass.states 
    WHERE entity_id = 'sensor.power_total'
    GROUP BY HOUR(FROM_UNIXTIME(last_updated_ts))
) t;

Apex-charts data-generator:

    data_generator: |
      const today = new Date();
      const maps = entity.attributes.hourly_averages.split(',');
      return maps.map((state, index) => {
        return [today.setHours(index), state]
      });
2 Likes
5

Would you be able to share the full YAML to the fancy Apex card above ?

1 Like
6

Would you be able elaborate further on this.
I’ve tried create a sql sensor, but it complains the SQL query is invalid.

7

This is exactly what I look for. Can you please share more information?

8

Thank you for posting this thread. I also had a similar goal of displaying a chart of my average water usage by hour of the day. I am still using the default Home Assistant sqlite database. I wasn’t able to use FROM_UNIXTIME() and I think my tables or entities are configured slightly different from yours. Using the SQLite Web Add-on helped to troubleshoot my query.

I had to do an additional lookup to get the attributes_id of my sensor from the state_attributes table, since my states table had NULL for all entities_id.

I ended up manually calculating the hours. ( local timezone offset: +54000, convert ts seconds to hours /60/60, mod division to get hour of day %24) I know this will have problems around daylight savings time.


SELECT AVG(state) as total_average, GROUP_CONCAT(state ORDER BY hour) as hourly_averages
FROM (
SELECT ROUND(AVG(state),2) as state, ((last_updated_ts+54000/60/60) %24 ) as hour
FROM "states" 
WHERE attributes_id = (SELECT attributes_id FROM "state_attributes" WHERE shared_attrs LIKE "%water_3%")
GROUP BY hour
) t;

I used the SQL integration with hourly_averages as the column