I´m searching for a solution to build the yesterdays average temperatur senssor.
I used up to now:
- platform: average
name: out_temp_average_yesterday
end: '{{ now().replace(hour=0).replace(minute=0).replace(second=0) }}'
duration:
hours: 24
entities:
- sensor.out_temperature
The integration dosen´t work since 2023.4 and I would like to switch to core functions of HA.
The statistics sensor isn´t helpful since it creates a “moving average” and the is no configuration for an start and end. So “yesterday” is not definable.
So I think it should be able with the SQL sensor plattform (via UI).
SELECT
states.state
FROM
states
WHERE
metadata_id = (
SELECT
metadata_id
FROM
states_meta
WHERE
entity_id = 'sensor.out_temperature'
)
ORDER BY
state_id DESC
LIMIT
1;
How to define to get only the values of yesterday?
How to define average (AVG) of values of yesterday?
This is not working, but something like this:
sql:
- name: 'at_gleitend_last_24h'
query: >
SELECT AVG(state) as state
FROM states
WHERE entity_id = 'sensor.out_temperature'
AND state != 'unknown'
AND state != ''
AND created > datetime('now().replace(hour=0).replace(minute=0).replace(second=0) ', '-24 hours')
GROUP BY DATE(entity_id);
column: 'state'
unit_of_measurement: "°C"
Thank you so much for your help!