Sensor history Last_Updated Null

Configuration
1

Background

I have been tracking the input of my Nvidia Shield as a sensor I created in configuration.yaml:

  - sensor:
      - name: "TVtracker"
        unique_id: tv_tracker
        state: "{{state_attr('media_player.supertv', 'source')}}"

This creates a nice history trail for which input I am using when:

image

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:

image
image2425×185 12.5 KB

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.

Thanks all in advance!

2

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;

image
image984×135 19.2 KB

Hope that helps!

3

@Soven Thank you for the suggestion but it says that from_unixtime is not a recognized function :frowning:

4

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

5

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;

2 Likes