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;
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
Ok ok, i got it. I am running HAOS with SQLite (bad idea?) and my query needed to be modified. Here is my SQLite config:
sql:
- name: consumption_ht
unique_id: consumption_ht
column: "hourly_averages"
query: >
SELECT AVG(state) as total_average, GROUP_CONCAT(state ORDER BY hour) as hourly_averages
FROM ( SELECT ROUND(AVG(state), 0) as state, strftime("%H",datetime(last_updated_ts,"unixepoch")) as hour
FROM states INNER JOIN states_meta ON states_meta.metadata_id=states.metadata_id
WHERE states_meta.entity_id = 'sensor.shellypro3em_ht_total_active_energy'
GROUP BY strftime("%H",datetime(last_updated_ts,"unixepoch"))
)
The above is a good example but it shows the average over all recorder data.
If you want to project the current electricity usage per hour on top of the graph with electricity rates of the day you need the following SQLite statement:
SELECT AVG(state)/1000 as total_average, GROUP_CONCAT(state ORDER BY hour) as hourly_averages
FROM (SELECT ROUND(AVG(state)/1000, 3) as state, strftime("%H",datetime(last_updated_ts,"unixepoch","localtime")) as hour
FROM states INNER JOIN states_meta ON states_meta.metadata_id=states.metadata_id
WHERE states_meta.entity_id = 'sensor.p1_meter_5c2faf102094_active_power'
AND date(last_updated_ts,"unixepoch","localtime") = date("now","localtime")
GROUP BY strftime("%H",datetime(last_updated_ts,"unixepoch"))
);
I don’t think an SQL query is necessary for anything in this thread. If you have a power sensor correctly configured, it should generate statistics. The average (and min and max) will be calculated hourly and stored indefinitely in the long term statistics table (regardless of your recorder purge setting), and apex charts can plot that data. And so can a statistics card.
Also, average power over an hour period is also the exact same thing as the energy consumed over that period, which is what the energy dashboard reports. (And the energy dashboard shows data from long term statistics.) So you should already have this data visible if you have energy sensors properly configured; you’d only need Apex charts if you want to add another item on the graph like energy price.
That is absolutely true. However using the apexcard handling the data and calculate the average significantly slows down the frontend.
Creating an SQL sensor doing the trick in just miliseconds for you is a way better ofloading the frontend interface
My point was that there shouldn’t be any calculations. The values that are desired to be plotted are already calculated and sitting in the statistics table. They just need to be plotted. There is no difference in burden on the frontend between pulling data from the states table compared to pulling the same data from the statistics table.
…but we are looking for average hourly over a period of time. So let’s say the average consumption between 12.00-13.00, on average, over the last 30 days. Your suggested solution doesn’t give us that right?
Thanks for posting this. The SQL Integration seem to accept this, but how do you then access this newly created sensor? The integration says that it’s not available. How do you access it? Tia.
I made a query that uses the statistics table, change the -1 year, to whatever suits your needs.
SELECT AVG(average) as grand_average, GROUP_CONCAT(average, ",") as hourly_averages_concat
FROM (
SELECT strftime("%H",datetime(created_ts, "unixepoch")) as hour, ROUND(AVG(mean) * 1000,2) as average
FROM statistics
INNER JOIN statistics_meta
ON statistics_meta.id=statistics.metadata_id
WHERE statistics_meta.statistic_id = 'sensor.electricity_meter_power_production'
AND datetime(created_ts, "unixepoch") > DATETIME('now', '-1 year')
GROUP BY hour
)
YAML of chart (that renders both average import & export per hour)
