I have just started running home assistant, but I have been using postgreql for years…
I notice that the states table has last_updated and last_updated_ts fields where the first is defined as time stamp with time zone and the other as double precision. In my data base, the first one is always empty and the epoch time is stored in the latter. Is it somehow possible to make home assistant store the data for last_updated as well? I am not finding any setting for that, but it seems a bit strange to define a column that is never to be used. I could of course write a trigger that updates the last_updated when a record is inserted, but if there is a setting somewhere, that would be preferable.
I believe you are observing the hard reality of the life of a project. The database structure in Home Assistant is an area where it is difficult if not impossible to have ‘breaking’ changes between releases. It needs to work with pretty much every historical version of HA. So what I think you are seeing is a recent change in the format of several time related columns. The old columns remain but are no longer used. I am guessing you are more versed in Postgresql than I am, but FYI, this is a new view I use to show only the new columns in a more useful way (it also addressed another change where the attributes column is now in a separate table) in the ‘states’ table. Good hunting!
SELECT states.last_updated_ts,
to_char(to_timestamp(states.last_updated_ts), 'YYYY-MM-DD HH24:MI:SS'::text) AS "last update",
states.entity_id,
states.state,
states.state_id,
state_attributes.shared_attrs
FROM states
LEFT JOIN state_attributes ON states.attributes_id = state_attributes.attributes_id
ORDER BY states.last_updated_ts DESC;