I have purchased a Shelly Pro 3 EM to measure the total electricity consumption in the house. It is integrated and working so far. I can see the values in the Energy Dashboard.
I would now like to have a new sensor that summarises my total consumption from October last year to October this year. I’ve searched a lot and also asked ChatGPT for help, but unfortunately I haven’t found a good solution yet.
My last approach was to try the Utily Meter, but it doesn’t support “months”.
You can use a cron tab (see documentation) to reset the Utility Meter at a specific time.
So in your case probably something like this: “59 23 30 09 *”.
This should reset the utility meter every 30th of September at 23:59.
However, I would probably use an SQL sensor for this. You probably already have the data in your Home Assistant database anyway. In my experience, this works more reliably than with utility meters and SQL is easier for me to understand due to my job.
In my case (I use a postgres DB) the sensor would probably look like this:
sql:
- name: "Electricity meter energy consumption october last year to current october"
unique_id: d5e93fc2-06ea-4d29-b90a-546ccfd7f742
unit_of_measurement: "kWh"
device_class: energy
db_url: !secret "db_url"
query: >
SELECT
max(statistics.sum) - min(statistics.sum) AS v1
FROM statistics
INNER JOIN statistics_meta
ON statistics.metadata_id = statistics_meta.id
WHERE
statistics_meta.statistic_id = 'sensor.esp_01s_stromzaehler_total_energy_in'
AND to_timestamp(start_ts) BETWEEN make_date(EXTRACT(YEAR FROM CURRENT_DATE)::int - 1, 10, 1)
AND make_date(EXTRACT(YEAR FROM CURRENT_DATE)::int, 10, 31)
column: "v1"
Do you need this to be a separate sensor or you just want to display the value in the dashboard? If it’s the latter, you can use statistics card.
In your case your would need to set the type to “change” and use fixed period.
I assume you already have some kind of total energy sensor in the shelly device, though I think it should work fine also with energy sensors that reset daily etc.
Seems like you didn’t read my answer properly
You can also use the utility meter and reset it via Cron at a specific time.
That’s just something I personally wouldn’t use
I need the value as a sensor, because I want to use it further, e.g. to be notified when a certain value has been exceeded.
@CM000n
I have read your message and realised this with the cron job. I just thought that there should be a simpler solution, as I can manually select a time window in the energy dashboard and the appropriate consumption is then displayed. So if the energy dashboard can already do this automatically, why does the manual process have to be so complex?
The “Magic” behind the energy dashboard is SQL
If you select a time range in the dashboard it creates and executes a query in the background against the database with it for you.
Unfortunately, to my knowledge at least, there is currently no integration that allows the content of an SQL sensor query to be created via the GUI.
Presumably simply because you can theoretically use SQL to query all existing values (not just enery dashboard related) from the database and manipulate them almost at will. This would probably make a GUI-based integration for creating sensors based on SQL queries either extremely complex to develop or extremely limited in it’s functionality.
I have followed your advice and inserted the sensor with your code.
With your code I got the following error:
Logger: homeassistant.components.sql.sensor
Quelle: components/sql/sensor.py:365
Integration: SQL (Dokumentation, Probleme)
Erstmals aufgetreten: 13:57:23 (8 Vorkommnisse)
Zuletzt protokolliert: 14:00:53
Error executing query SELECT max(statistics.sum) - min(statistics.sum) AS v1 FROM statistics INNER JOIN statistics_meta ON statistics.metadata_id = statistics_meta.id WHERE statistics_meta.statistic_id = 'sensor.stromzaehler_total_active_energy' AND to_timestamp(start_ts) BETWEEN make_date(EXTRACT(YEAR FROM CURRENT_DATE)::int - 1, 10, 1) AND make_date(EXTRACT(YEAR FROM CURRENT_DATE)::int, 10, 31) LIMIT 1;: (sqlite3.OperationalError) near "FROM": syntax error [SQL: SELECT max(statistics.sum) - min(statistics.sum) AS v1 FROM statistics INNER JOIN statistics_meta ON statistics.metadata_id = statistics_meta.id WHERE statistics_meta.statistic_id = 'sensor.stromzaehler_total_active_energy' AND to_timestamp(start_ts) BETWEEN make_date(EXTRACT(YEAR FROM CURRENT_DATE)::int - 1, 10, 1) AND make_date(EXTRACT(YEAR FROM CURRENT_DATE)::int, 10, 31) LIMIT 1;] (Background on this error at: https://sqlalche.me/e/20/e3q8)
I have therefore adapted the code as follows:
sql:
- name: "Stromverbrauch von 01.10 bis 31.10"
unique_id: d5e93fc2-06ea-4d29-b90a-546ccfd7f742
unit_of_measurement: "kWh"
device_class: energy
query: >
SELECT
max(statistics.sum) - min(statistics.sum) AS v1
FROM statistics
INNER JOIN statistics_meta
ON statistics.metadata_id = statistics_meta.id
WHERE
statistics_meta.statistic_id = 'sensor.stromzaehler_total_active_energy'
AND datetime(statistics.start_ts, 'unixepoch') BETWEEN datetime(strftime('%Y', 'now') || '-06-24')
AND datetime(strftime('%Y', 'now') || '-06-25')
column: "v1"
I have adjusted the time period for testing. I noticed that the sensor is not giving me the correct values.
According to the energy dashboard, I consumed this much kWh on the following days:
23.06.24 - 7.73 kWh
24.06.24 - 6.63 kWh
25.06.24 - 5.82 kWh
But the sensor shows me the following kWh.
6.52167 kWh
I cannot assign this value to any of the days. Is this due to my adjustments to the code or what could be causing the error?
What Database are you using?
My query quite likely contained code that only works with a PostgresDB.
To understand exactly what a query does, it always helps to look at the data before aggregation. Do you have a way of accessing the database itself via a database tool?
In general, your query will no longer give you the data between the beginning of October last year and the end of October of the current year, as you originally requested.
You now query all statistical data between 24 and 25 June of the current year (closed interval).
Given the daily values you have written here, I would expect the following result
24.06.24 - 6.63 kWh + 25.06.24 - 5.82 kWh = 12,45 kWh
Question:
Does the sensor.stromzaehler_total_active_energy you are using continuously count up the energy measurement, or does it also reset itself?
Or to put it another way, does this sensor measure the current total meter reading of your electricity meter?
If yes, your query should actually fit. If no, you would have to modify your query a little.
I am using the default installed database SQLite. I have never changed anything here at least.
I have installed the SQLite Web add-on and can theoretically access the database with it. As I said, I’m not familiar with SQL queries, so I’ll have to familiarise myself with this first.
I am aware that my query does not give me exactly the data I first requested (from October to October). As already mentioned, I minimised the time period in order to better search for the error. I did this because the original value (from October to October) was also a different value that was displayed in the energy dashboard.
The sensor counts up continuously and does not reset itself or through HA.
The sensor measures the value since I put it into operation. I.e. it was at 0 kWh on 20/06/2024 and is now at 39.58822 kWh
Sorry. I missed the part where you mentioned that you adjusted the time period for testing purposes.
I think I have found your problem. Your query converts the timestamps with their UTC value. So the energy values are shifted by 2 hours (since you have German language in your logs, you probably want the times for Europe/Berlin ):
In addition, it seems to be a special case of SQLite that it uses an open and not a closed interval for BETWEEN queries and does the conversion wrong. It might work if you also convert the values to date after you have converted them to datetime. sql - SQLite database - select the data between two dates? - Stack Overflow
So you didn’t query the data from 2024-06-24 to 2024-06-25 with your query, but the data from 2024-06-24 00:00:00 to 2024-06-24 23:59:59.
In addition, you have not queried values in local time, but the values in UTC.
I believe that the different time zones are the cause of the problem, but no matter how I design the SQL query, I can’t get the same values as in the energie-dashboard.
I have tested it with localtime +02:00 and also with date(datetime(…)) as well as with date(…) without datetime and just can’t get the same values as in the energy dashboard.
I think I will have to live with the fact that my values are not 100% accurate.
So it looks as if the Energy Dashboard is using values from the states tables for current time periods, such as today or this week.
For longer periods, such as months or years, the values from the statistics tables are used.
By default, the values in the states tables are deleted after 10 days, so that only the information from the statistics tables is available for values before this time.
It might work if you also convert the values to 
