Database column names have changed

I apologize if this is not the correct forum for this question. It seemed the closest fit.

The SD card on my Pi, which was running a very old version of HA, crashed beyond repair. I have a backup, but since it was such an old version I decided to just rebuild with the latest, greatest version (2023.3.5).

All is going well except for my SQL pseudo-sensors. Most of them use a time-limited query (using the datetime function) such as this one for 1-hour rain totals:

select abs(round((select state from states where entity_id = 'sensor.cumulative_rain' and state <> "unknown" order by created desc limit 1)-(select state from states where entity_id = 'sensor.cumulative_rain' and state <> "unknown" and created < (datetime('now','-1 hours')) order by created desc limit 1),2)) as 'delta';

The problem is, the created column no longer exists in the new version of HA. I thought I could substitute the last_changed or last_updated column, but all of the rows have blanks in those columns (side question: what do those columns represent, and what is the difference between them?). I also tried substituting last_updated_ts and last_changed_ts (same side question for these), but, although the rows have values in last_updated_ts I can’t seem to make my queries work for them.

Any advice on fixing my queries?

The structure of the states and events tables have been significantly changed in the last several HA updates. The date and time fields in these tables now use new columns that contain a data type of float value that represents a time stamp. I use Postgresql for my HA database so the syntax may be different, however for my queries I am now using something like below to convert this float to a timestamp. Note it is a new field as well, the old timestamp fields remain in the database, however are not used or updated. Good hunting!

WHERE (entity_id = 'sensor.random_quote'
    AND state != 'unavailable'
    AND state != 'unknown'
    AND to_timestamp(last_updated_ts) > now() - interval '5 days')
1 Like