Hi everyone,
I seem to be encountering a serious issue with my Home Assistant Recorder that appears to be causing significant problems with my historical data and potentially database growth. I’m hoping someone might have seen this before or can offer some guidance.
My home-assistant_v2.db file is showing a large number of entries in the states table where the entity_id, last_changed, and last_updated columns are consistently NULL. However, the last_updated_ts column (the Unix timestamp) is being populated with what appear to be correct timestamps.
When I query my database using DB Browser for SQLite with this command:
SELECT state_id, entity_id, state, last_changed, last_changed_ts, last_updated, last_updated_ts FROM states LIMIT 10;
I get results like this:
| state_id | entity_id | state | last_changed | last_changed_ts | last_updated | last_updated_ts |
|---|---|---|---|---|---|---|
| 1 | NULL | off | NULL | NULL | NULL | 1746666171.10254 |
| 2 | NULL | on | NULL | NULL | NULL | 1746666171.10538 |
and so on for many entries.
NULL entity_ID means recorded states aren’t linked to my actual devices and entities, making historical data useless. Without proper last_changed timestamps, the purge_keep_days setting may not function correctly.
I recently deleted my entire database to start fresh, but the issue persists even with 1 day’s worth of data. Checked my timezone, did a lot of rebooting.
Currently using Home Assistant Green, core 2025.4.4, HAOS 15.2.
Has anyone encountered such an issue where the Recorder is failing to save the entity_id and last_changed timestamps? Any ideas on what might be causing this or how to troubleshoot it further?
Any help or insights would be greatly appreciated. Thanks.