I wanted to give my feedback here, maybe it helps someone.
I experimented with the sensors and code described in this thread and developed a strategy to monitor my consumption and generation.
Before that I had several entities for each consumption/generation, a “forever” meter, a “since date x” meter (which has to be the same date for all), a “plan value” and a “calculated yearly value”.
I needed the because only with a defined (and same) start date for each meter I could calculate the consumption for the “rest if the house” (consumption minus all measured devices)
It was all possible in the UI but had several flaws, at the beginning of a period the calculated value is inaccurate, especially there are significant changes in consumption during seasons, e.g. as for heat pumps.
What I did now is I made a SQL sensor for rolling yearly consumption and one for 30 day average. With the help of the base code of this thread and ChatGPT I have a result that seems to work and is quite satisfying for my needs.
- I can monitor the change in the last days or weeks and it makes sense as a yearly rolling window is not affected by seasonal changes in consumptions
- I can start with all history that I have as the sensor checks for the older values and if these are less then a year old it takes the oldest ones and considers that in the calculations
- I can check in the attributes from when the first value is
Here is an example how it looks like:
“Haus Rest” is an example where the data only started at creation of the sensor, so of course it changes significantly but should get closer and closer to a value where it makes sense; the heat pump is an example where I have almost a whole year of data.
Here is the example code:
sql:
- name: Verbrauch Strom gesamt Jahr rollierend
unique_id: verbrauch_strom_gesamt_jahr_rollierend
unit_of_measurement: kWh
icon: mdi:home-lightning-bolt
device_class: energy
state_class: total
query: >
SELECT
CASE
WHEN fallback_365 IS NOT NULL THEN current_value - fallback_365
ELSE (current_value - fallback_fixed) * (365.0 / ((strftime('%s','now') - fallback_fixed_ts) / 86400.0))
END AS value,
datetime(COALESCE(fallback_365_ts, fallback_fixed_ts), 'unixepoch', 'localtime') AS reference_timestamp,
((strftime('%s','now') - COALESCE(fallback_365_ts, fallback_fixed_ts)) / 86400.0) AS days_since_reference
FROM (
SELECT
(SELECT s.Sum
FROM statistics s
INNER JOIN statistics_meta m ON s.metadata_id = m.id
WHERE m.statistic_id = 'sensor.verbrauch_strom_gesamt'
ORDER BY s.start_ts DESC
LIMIT 1) AS current_value,
(SELECT s.Sum
FROM statistics s
INNER JOIN statistics_meta m ON s.metadata_id = m.id
WHERE m.statistic_id = 'sensor.verbrauch_strom_gesamt'
AND s.start_ts <= strftime('%s','now','-365 days')
ORDER BY s.start_ts DESC
LIMIT 1) AS fallback_365,
(SELECT s.start_ts
FROM statistics s
INNER JOIN statistics_meta m ON s.metadata_id = m.id
WHERE m.statistic_id = 'sensor.verbrauch_strom_gesamt'
AND s.start_ts <= strftime('%s','now','-365 days')
ORDER BY s.start_ts DESC
LIMIT 1) AS fallback_365_ts,
(SELECT s.Sum
FROM statistics s
INNER JOIN statistics_meta m ON s.metadata_id = m.id
WHERE m.statistic_id = 'sensor.verbrauch_strom_gesamt'
ORDER BY s.start_ts ASC
LIMIT 1) AS fallback_fixed,
(SELECT s.start_ts
FROM statistics s
INNER JOIN statistics_meta m ON s.metadata_id = m.id
WHERE m.statistic_id = 'sensor.verbrauch_strom_gesamt'
ORDER BY s.start_ts ASC
LIMIT 1) AS fallback_fixed_ts
) sub;
column: value
value_template: "{{ value | round(2) }}"
- name: Verbrauch Strom gesamt 30 Tage Durchschnitt
unique_id: verbrauch_strom_schnitt_30_tage
unit_of_measurement: kWh
device_class: energy
state_class: total
icon: mdi:transmission-tower
query: >
SELECT
(SELECT statistics.Sum
FROM statistics
INNER JOIN statistics_meta ON statistics.metadata_id = statistics_meta.id
WHERE statistics_meta.statistic_id = 'sensor.verbrauch_strom_gesamt'
ORDER BY statistics.start_ts DESC
LIMIT 1)
-
(SELECT statistics.Sum
FROM statistics
INNER JOIN statistics_meta ON statistics.metadata_id = statistics_meta.id
WHERE statistics_meta.statistic_id = 'sensor.verbrauch_strom_gesamt'
AND statistics.start_ts > UNIXEPOCH("now", "-30 days", "-1 hour")
LIMIT 1)
AS change
column: change
value_template: "{{ (value / 30) | round(2) }}"
Appreciate your feedback.