Yesterdays average temperature SQL sensor platform

Configuration
1

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!

2

Consider querying the statistics table which has hourly values with mean, min and max and use CURDATE() in where clause. Using AVG of mean results in day average. Metadata_id comes from table statistics_meta and identifies your sensor.

Something like

select
    date_format(from_unixtime(start_ts),'%Y-%m-%d' ) day
   , avg(mean)
   , min(min)
   ,max(max)
from
    statistics
where
    metadata_id in (32)
    and from_unixtime(start_ts) > (CURDATE() - 1)
    and from_unixtime(start_ts) < CURDATE()
group by
    day;
3

I tried, but dos not work. I do this in intergration UI.

grafik
grafik826×426 20.1 KB

no such function: from_unixtime

4

Ah, right I provided mariadb sql syntax, my bad. Try like this one instead, that worked in my test hass

select 
 	AVG(mean) state 
from
 	statistics_meta as m inner join statistics s on m.id=s.metadata_id
where 
	statistic_id = 'sensor.out_temperature'
	and DATE(start_ts,'unixepoch') >= DATE('now','-1day') 
	and DATE(start_ts,'unixepoch') < DATE('now')
group by
	DATE(start_ts,'unixepoch'),statistic_id