How to calculate average hourly energy consumption over multiple days?

Configuration
,
1

Hi everyone,

I have been logging my energy consumption for a long time in Home Assistant.
Now, I would like to calculate the average energy consumption for each specific hour across multiple days.

For example:

  • What is my average consumption between 12 PM and 1 PM over the last 3 months?
  • Ideally, I want a table showing each hour of the day (00–01, 01–02, …, 23–00) with the corresponding average consumption.

I understand that the data is stored in the home-assistant_v2.db SQLite database.
Is there a built-in way in Home Assistant to generate this (using templates, statistics, SQL integration, etc.)?
Or would I need to manually query the database with an SQL statement?

Any guidance or examples would be greatly appreciated!

Thank you! :blush:

2

It is an interesting question and not even sure why I never wanted to have this insight. fafaik there is no card or so that can do this so I guess you are stuck with SQL statements. This however means that you will have to create a SQL sensor for each slot (00:00-01:00, 01:00-02:00, etc.), this as SQL integration only kicks back one value per sensor.
My suggestion would then be to have the dates for the period based on a sensor, i.e. it cannot use templates but can read the value for the helpers (start/end) from the db

As I (by far) donot know what has ben created for HA, maybe someone else has another idea/solution

3

I would like to have this data to better plan the size of the battery storage for my future photovoltaic system.
Knowing the average consumption for each hour of the day will help me understand my typical energy usage patterns and determine how much storage capacity I really need, especially to cover consumption during the evening and night hours.

4

I get it, I myself was thinking if one could change daily habits that are difficult to identify …e.g. we still kick-off the dishwasher after hours when the solar panels are ‘out’…this one I know but there may be a lot more and having insight in (changing) hourly averages may help too.
So, if you are fit enough for SQL then I happily await your solution to re-use it. If not, then I can spend a bit of time on it myself too. The only issue I have with SQL is that in this case one needs 24 sensors if one seeks a 24-covering overview. Otherwise one could create a SQL that could do this based on starthour-endhour … but then you have no graph option