HA SQL Get Results X time ago - Works with this code

Configuration
1

Just as Info in case someone had similar problems

I wanted to get the values of a sensor that measures the electricity consumed, sold and heatpump 1 day ago, 2 days ago and 3 days ago. The sensors used as base resets to 0 at midnight.

I was using the code suggested in the SQL Integration page. It was working and then stopped. Couldn’t find why since I hadn’t changed anything - just updated HA to the latest UI and core.

Here is the code that works now - example for electricity, and getting the values for 2 days ago

SELECT
  states.state
FROM
  states
  INNER JOIN states_meta ON
    states.metadata_id = states_meta.metadata_id
WHERE
  states_meta.entity_id = 'sensor.from_netz_daily'
  AND last_updated_ts <= strftime('%s','now','-1 day','start of day')
  AND last_updated_ts >= strftime('%s','now','-2 day','start of day')
ORDER BY
  CAST(states.state as numeric) DESC
LIMIT
  1;

Just for reference, here is the code I had used before. Basically I now use an interval and I use orderer by states.state and CAST.

SELECT
      states.state
    FROM
      states
      INNER JOIN states_meta ON
        states.metadata_id = states_meta.metadata_id
    WHERE
      states_meta.entity_id = 'sensor.from_netz_daily'
      AND last_updated_ts <= strftime('%s', 'now', '-1 days', 'start of day', 'utc')
    ORDER BY
      last_updated_ts DESC
    LIMIT
      1;