MariaDB last_updated is null

Hello all,

in my DB is the no Date/Time of the action. I use the Add-on of the Maria DB and I did a reinstall. No success :frowning:

Do you have any advice for me?

Home Assistant OS 2023.2.3

It is last_updated_ts since recently

Just ran into the Issue of Grafana not fetching data as it took way to long and just hung. Only just now finding that they changed the last_changed to last_changed_ts and causing huge issues with getting a TIME STAMP for Grafana.

Happy Happy joy joy…

No Idea how to fix this and after messing with this I am pretty much just fed up with it

I donot have those issues… I assume you mean Grafana fetching data from Influxdb? Example?

It’s Epoch time, for mysql this works but looking up conversion from epoch should get you where you want to go.

SELECT from_unixtime(last_updated_ts), last_updated_ts FROM states ORDER BY states.state_id DESC;
image

In SQLite the conversion can be done with

datetime("last_updated_ts", "unixepoch")

his is equivalent to

datetime("last_updated_ts" / 86400.0  + 2440587.5)

Here is an example:

SELECT   datetime("last_updated_ts","unixepoch"), 
         datetime("last_updated_ts"/ 86400.0  + 2440587.5) , 
         "last_updated_ts"
FROM "states"
ORDER BY "state_id" DESC