Yesterdays average temperature SQL sensor platform

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!

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;

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

no such function: from_unixtime

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