SQL to get previous entity state

Configuration
1

Hi All,

I’m trying to use a bit of SQL to grab the cost of electricity over the last 5 days which is stored as an entity, but overwritten on a daily basis.

I’ve got the following SQL - but I’m getting an invalid sql statement error when trying to run it using the home assistant SQL integration - if it helps, I’m expecting a numerical value - any ideas?

SELECT * 
FROM
  states
WHERE
  entity_id = 'sensor.electricity_previous_accumulative_cost' 
ORDER BY ABS(strftime('%s', created) - strftime('%s', datetime('now'), '-1 day'))
LIMIT
  1;

Original SQL code taken from this thread - Show sensor value from 24 hours previous

I’m using the built-in HA Database if it helps.

Thanks
Leacho

2

entity_id is no longer used like this, it is in table states_meta so you have to map that

3

The documentation for the SQL Sensor has been updated to show the current way of doing it. It changed from 2023.4.

1 Like