SQL - avg value time interval

Configuration
1

Hi,
I have a sensor that’s trace my house kW. This i called: sensor.huseffekt_exl_elbil

I want to use this sensor to calculate the avg value of a time intervall.

The query below gives me a avg value (I think)

    SELECT AVG(CAST(states.state AS FLOAT)) as avg
    FROM states
    JOIN states_meta ON states.metadata_id = states_meta.metadata_id
    WHERE states_meta.entity_id = 'sensor.huseffekt_exl_elbil'
      AND states.state NOT IN ('unknown', 'unavailable')

But when I try to calculate the avg value of a time intervall I get unknown.

SELECT AVG(CAST(states.state AS FLOAT)) as avg
    FROM states
    JOIN states_meta ON states.metadata_id = states_meta.metadata_id
    WHERE states_meta.entity_id = 'sensor.huseffekt_exl_elbil'
      AND states.state NOT IN ('unknown', 'unavailable')
      AND states.last_changed >= '2025-06-23 00:00:01'
      AND states.last_changed <= '2025-06-23 23:59:59'

My goal is to calculate the avg value from a time intervall that a have in two sensors. The sensor updates so it’s dynamic value. Is that possible?

The sensors I want to use is: sensor.expensive_price_interval_1 and sensor.expensive_price_interval_2

And both of them has this timeformat:
2025-06-25T16:00:00+02:00 - 2025-06-25T23:59:00+02:00

2

imo sqlite doesn’t provide interval datatype. thus time-time does’t result in interval.

Another problem is the join itself. you need to join the related records. joining just by entityid is not enough since every entity has multiple records.
you could add time condition to the join, but I doubt both sensors are recorded at exact same time (such assumption would be too optimistic)

imo you can create a template sensor calculating the difference. then you can calculate average or other aggregates from results of this template sensor

3

I realise I was a little unclear. My goal is to get the avg value for that time intervall for the last three days.

Can I use two input_datetime? In that way I get the start and end value. And from there calculate the avg kW for the past three days.

I will use the avg value to estimate how many kWh my battery mus have before an expensive interval.

I have two expensive intervall som i want to calculate two avg value