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.

#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]
      });
1 Like
#5

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

1 Like