SQL query after 9 days

Configuration
1

I’m trying to query the sensor.fordpass_odometer from 30 days ago. I’m running latest HAOS. The below query works if I change it to “-9 days”, but -10 or -30 does not return anything (logs show it returns nothing). I can graph that sensor.fordpass_odometer and I do see valid values for the past 30 days. I use the SQL integration with the column set to “state”. Do I have to query data older than 10 days in a different way?

SELECT
states.state
FROM
states
INNER JOIN states_meta ON
states.metadata_id = states_meta.metadata_id
WHERE
states_meta.entity_id = ‘sensor.fordpass_odometer’
AND last_updated_ts <= strftime(‘%s’, ‘now’, ‘-30 days’)
AND state IS NOT NULL AND state NOT IN (“unknown”, “unavailable”)
ORDER BY
last_updated_ts DESC
LIMIT
1;

2

Assuming the sensor in question is set up properly, anything before the purge limit of the Recorder (which is 10 days by default), is part of long-term statisitcs.

3

I’m starting to think the SQL examples on the current docs refer to the statistics_short_term table query. I suspect the data in the statistics long term table are different format.