SQL Integration Sensor wrong value

Configuration
1

Hi there,

I want to caluclate my daily power consumption based on a power outlets wattage updating every second. I testet my query directly on my database which gives me the correct value:

select
  SUM(cast(state as float)) / 1000 / 3600 as Summe
FROM
  states
WHERE
    metadata_id = (
        SELECT
          metadata_id
        FROM
          states_meta
        WHERE
          entity_id = 'sensor.power_14'
      )
    AND cast(to_timestamp(last_updated_ts) as date) at time zone 'utc' = date('now') at time zone 'utc';

which gives me (by the time I’m writing) ~3,58
but HA reports a value of ~2,64

image
image1075×710 46.4 KB

I fiddled around with the timestamps and also created a view in the database using that query, but the value in HA is always wrong.

what am I doing wrong?

Thanks in advance

Michael