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.
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:
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.
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;