This creates a nice history trail for which input I am using when:
My goal is to use the historical data to calculate the best guess of which input I want at a given time. For example if I turn on the tv now, it will look up in the past 7 days which input was the most used and try and turn it to that input.
Problem
I used to use a SQL statement to calculate this most likely input going off of the last_updated column from the states table but recently this field has become completely NULL:
This is a bit confusing as to why these values are now null and not time stamps. I am really unsure how to get the values to appear again? Alternatively I do see that there is a last_updated_ts that could be used as well but this value is reported as:
1675953672.503346
What kind of date format is this? This event happened today (2/10/2023) this morning so not sure how to convert correctly. Really two methods of trying to obtain what I want which is just a date / time to go off of.
Don’t know the technical name, but from epoch. If you’re using mysql
the bold will change it to UTC. SELECT state_id, entity_id, state, old_state_id, attributes_id, from_unixtime(last_updated_ts), last_updated_ts FROM states WHERE entity_id = sensor.energy_monitor_current_consumption ORDER BY states.state_id DESC;
Yes I too have lots of SQL sensors failed now since a recent update to Core that were using last_updated datetime fields and that now just seem to read NULL in that column and for it to have worked that column must have been full of date data.
So a simple SQL that used to work to read the value of a sensor a week ago now produces log errors
SELECT * FROM states WHERE entity_id = “sensor.total_electricity_energy_manual2” AND last_updated >= date(‘now’, ‘-7 days’) ORDER BY last_updated ASC LIMIT 1;
LOG:
SELECT * FROM states WHERE entity_id = “sensor.total_electricity_energy_manual2” AND last_updated >= date(‘now’, ‘-7 days’) ORDER BY last_updated ASC LIMIT 1; returned no results
No sensor seems to have anything but NULL in the last_updated column
Wow that was a bit detailed but sorted now but with little documentation and no examples I could find so this could be really useful for people wanting to get sensor data from the past :
SELECT * FROM states WHERE entity_id = “sensor.total_energy_manual2” AND DATETIME(last_updated_ts,‘unixepoch’) >= DATETIME(‘now’,‘localtime’, ‘-7 days’) ORDER BY last_updated_ts ASC LIMIT 1;