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?