State of an entity (sensor) x time ago

Configuration
,
1

Hi
I am looking quite some time now how to get sensor value that was presented 24 hours ago. And now i know SQL is the only option.
I found how to do this https://www.home-assistant.io/integrations/sql/#state-of-an-entity-x-time-ago

Except id does not work !

SQL Query invalid

Screen
Screen620×1033 54.4 KB

I took example code using copy “button” presenting itself on the top right corner.

and did only one change:
states_meta.entity_id = 'sensor.online_thermometer_temperature'

what is the most strange i have the same error when i try to submit original code form that example.

I tried to play with that query a lot, different spaces, different white marks, nothing works, any idea WHY?!?

2

Please share the query rather than a screenshot of part of it: even if you think it is the same, something may have changed on the way across.

Please format the code correctly by surrounding with three backticks (```) — should look like this but with your query:

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

The example worked perfectly for me: all I did was swap sensor.temperature_in for sensor.outside_temperature that exists on my system. Gives me the temperature 24 hours ago.

What version of HA are you running? The entity_id was de-duplicated out to the states_meta table about 10 months ago (ref), so if you’re running 2023.3 or earlier, that may be the issue.

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

I am using:

  • Core2024.1.5
  • Supervisor2023.12.1
  • Operating System9.5
  • Frontend20240104.0

I have played more and i know now that WHERE condition is the problem. If i remove

AND last_updated_ts <= strftime('%s', 'now', '-1 day')

integration is added

4

Can confirm that your exact query copied from above and swapping online_thermometer_temperature for outside_temperature works perfectly here.

Anything in your logs? You haven’t set up a different database engine (e.g. MariaDB) and forgotten?

5

Yes i have MairaDB and to be precise i already know that:

strftime('%s', 'now', '-1 day')

gives “SQL Query invalid” error, so it must be different for MariaDB, but i do not know how.

6
7

OK so for all coming here with the same problem. Original solution provided on SQL integration page that works for default Home Assistant database which is:

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

for MariaBD must be changed to:

SELECT
  states.state
FROM
  states
  INNER JOIN states_meta ON
    states.metadata_id = states_meta.metadata_id
WHERE
  states_meta.entity_id = 'sensor.temperature_in'
  AND last_updated_ts <= UNIX_TIMESTAMP(NOW() - INTERVAL 1 DAY)
ORDER BY
  last_updated_ts DESC
LIMIT
  1;

Thank you very much Troon for your help. You pointed me to the fact that MariaDB is the problem here, and to be exact problem is from fact that strftime() is not being supported for MariaDB and must be replaced with UNIX_TIMESTAMP()

2 Likes
8

I would like to add a small contribution here. If you want to have data longer than what is stored in your recorder database, you have to use the long term statistics database.

Use the SQL integration, column “mean” and use this query:

SELECT
  statistics.mean
FROM
  statistics INNER JOIN statistics_meta ON 
    statistics.metadata_id = statistics_meta.id 
WHERE
  statistics_meta.statistic_id = 'sensor.some_random_sensor' AND
  start_ts <= strftime('%s', 'now', '-30 days')
ORDER BY start_ts DESC LIMIT 1