I thought this might happen, but late last night and confirmed this morning I noticed that my Home Assistant statistics were no longer being collected. The Energy dashboard was blank for today and if I looked at individual sensor statistics in developer tools there were no new stats being collected every 5 minutes or hour.
There follows a brief writeup of the diagnosis and how I restored it. I learnt a bit more about the HA statistics routines today as a result so sharing in case you have similar issues, but the cause of my issue is hopefully something that never occurs to you.
Wind the clock back to last year and I was away for 2 weeks in August and early on HA stopped letting me connect remotely. When I got back I found that HA seemed to have lost all connectivity so restarted everything and apart from a big gap in data history I thought it was OK. Well except I started noticing weird things when I looked at sensor history, on some sensors e.g. battery SoC, I could see future values of the sensor running into 2025. And in the HA Core log, Supervisor Log and Host Log there were logfile records with dates again into February 2025 - but when I looked at the actual logs on the HA server there were none of these rogue date entries.
It was as if HA had fallen into a time machine. Maybe when it lost local connectivity back in August, that's my hypothesis, but since everything was now working apart from some weirdness on some graphs, I just left it running with no side effects.
More recently I noticed on the Energy graph that there were some big spikes occurring and I narrowed it down to the jumps started at 22:00 on 19/1/25 and then finished at 22:00 on 2/2/25. When I looked at the developer tools/statistics I could see the big negative values so I just kept resetting them to zero and the energy graph all looked OK.
In hindsight I should have looked further and wouldn't have had a day without any statistics being gathered.
Anyway last night the energy graph stopped working at 22:00, despite grid importing the graph stopped updating
And today, no stats collected at all. Here's one of the battery discharge sensors at 17:36, shows zero for every hour that has occurred today, but also zero going forward:
I tried restarting HA, running an integrity check on the database (it reported some errors) so I extracted and restored all the data back into the database, tried repacking the database, and none of these would get the statistics to start collecting. Moreover no messages in the HA core log about recorder or history processes not working.
Before going on, I found and installed db browser for sqlite https://sqlitebrowser.org/ which gives a very easy way of viewing and filtering data on the sqlite database, definitely easier to use than the Sqlite Web add-on for HA. I copied the database /config/home-assistant_v2.db to my PC using a samba share and used the db browser to get my head around the database contents.
First key table is statistics_runs which contains details of when the statistics gathering process has run:
I noticed that there are entries with run_id's up to 134328 (2025-01-19 21:55:00) and then run_id's from 119695 to 123738 corresponding to 2025-01-19 22:00:00 through to 2025-02-02 22:55:00
The run_id's are sequential so this jump backwards indicates that the entries from 21:55:00 were written to the table some time ago - i.e. when HA fell into a time warp.
I guessed that as there were records with a start time from 22:00 last night and all through the day today, the statistics gatherer thought that statistics had already been collected and so this is why I wasn't getting any statistics captured.
Take a backup of the database first!
I then deleted all the records from statistics_runs with dates from 2025-01-19 22:00:00 upwards to see if this unblocked the statistics module.
It did, a bit, I now got errors reported in the HA core log that the statistics module was now failing trying to insert duplicate records into the database:
Blocked attempt to insert duplicated statistic rows
sqlite3.IntegrityError: UNIQUE constraint failed: statistics_short_term.metadata_id, statistics_short_term.start_ts
[SQL: INSERT INTO statistics_short_term (created, created_ts, metadata_id, start, start_ts, mean, min, max, last_reset, last_reset_ts, state, sum) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) RETURNING id]
[parameters: (None, 1737389894.1382382, 616, None, 1737324300.0, 3.25, 3.25, 3.25, None, None, None, None)]
(Background on this error at: https://sqlalche.me/e/20/gkpj)As mentioned in my other article on the database structure, statistics_short_term is the short term statistics captured every 5 minutes. I was getting an error every 5 minutes now about trying to insert a record that had the same unique metadata_id and start_ts as another record, i.e. the short term statistics records already existed.
The records are written with a unix timestamp of when they are created (created_ts) and the time that the statistics applies to (start_ts)
Wrote a bit of template code in developer tools/statistics to decode the start_ts into readable format:
{% set x=1737324300.0 %}
{{ x | timestamp_custom('%Y-%m-%d %H:%M') }}2025-01-19 22:05
Which makes sense, its trying to write the next short term statistics record and failing as it already exists.
So now to delete all the short term stats records that shouldn't be there:
select count(*) FROM "statistics_short_term" where start_ts>1737323999;
to confirm number of records that will be removed
delete FROM "statistics_short_term" where start_ts>1737323999;
removed 1075118 rows dated from 19/1/25 to 2/2/25
and the short term statistics errors stopped being produced and I could see that the short term stats were now being collected OK:
next need to similarly tackle the long term statistics which are captured hourly and are held in the table statistics
Before I did anything I wanted to check the statistics for a specific sensor to check the database matched what I was seeing via developer tools/statistics
SELECT * FROM "statistics_meta" where statistic_id like '%total_kwh'
to find the statistics metadata id for a battery discharge sensor, it was 96
SELECT * FROM "statistics_short_term" where metadata_id=96 order by start_ts
to see short term statistics for battery discharge - first start_ts is 11/1/25, there's 10 days of short term stats retained
SELECT * FROM "statistics" where metadata_id=96 order by start_ts
to see long term statistics for battery discharge -first start_ts is 1698030000 23/10/23 04:00
last entry is 1738533600 2/2/25 22:00
Long term stats been collected from when I first started with HA (actually it was about a month before that but the database got corrupted and I had to start again), and most recent is this future dated entry of 2/2/25.
Bit of trial and error trying to find exactly where I wanted to slice records out of the statistics table, as it was now 17:40 I thought I would delete everything dated from 18:00 onwards (the cell selected):
Notice the line arrow'd, its 19/1/25 22:00, the incrementing id column jumps backwards and the state column reverts back to an older value.
I decided to leave the records from 19/1/25 22:00 through to 20/1/25 17:00 in the database so I could then fill them in later on with the correct history values.
First future date entry we want to remove is 1737396000.0 20/1/25 18:00
select count(*) FROM "statistics" where start_ts>1737395999;
Count the number of records records after 20/1/25 17:59, 84056 rows
delete FROM "statistics" where start_ts>1737395999
Delete these 84056 rows
Checked and yes the future statistics have now gone
And waited until 18:00 to see the new statistics being written, and instead of lovely statistics, a new SQL constraint violation error:
2025-01-20 18:00:10.197 WARNING (Recorder) [homeassistant.components.recorder.util] Blocked attempt to insert duplicated statistic rows, please report at https://github.com/home-assistant/core/issues?q=is%3Aopen+is%3Aissue+label%3A%22integration%3A+recorder%22
sqlalchemy.exc.IntegrityError: (sqlite3.IntegrityError) UNIQUE constraint failed: statistics.metadata_id, statistics.start_ts
[SQL: INSERT INTO statistics (created, created_ts, metadata_id, start, start_ts, mean, min, max, last_reset, last_reset_ts, state, sum) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) RETURNING id]
[parameters: (None, 1737396010.1632707, 2, None, 1737392400.0, 15.200554342555556, 15.2, 15.3, None, None, None, None)]decoding start_ts 1737392400.0, it transpires that the statistics module is creating the stats for 20/1/25 17:00 but with a created_ts of 1737396010.1632707 20/1/25 18:00 - i.e. it is creating the statistics retrospectively.
So I should have deleted the records for 17:00 as well so the 18:00 run would work. Instead I just left it until 19:00 and sure enough that run worked fine. It did create big stupid negative adjustments in the stats but I can fix that:
So there we go, a canter through how the Home Assistant statistics tables work and how to remove errant data if your HA ever falls into a time machine like mine did.