Funnily during several “events” (accidents / incidents) I had to correct data in the database many times meanwhile - and I’ve ended up using almost the same steps.
But this topic is a good description of the necessary tasks.
I just want to add:
Meanwhile DB schema has changed. Sometimes it might be necessary to take a look at the “new” table state_attributes too (usually not for fixing statistics data but likely for other purposes).
To fix things 100 % I always had to edit (in that order):
states table
statistics_short_term table
statistics table
Take caution when using date filters: the timestamps in the database are UTC, this might/will vary to your actual time (+/- X hours depending on your timezone).
I learned to usually never delete rows from any table, as this often leads to FK constraint violation which immediately renders the database inconsistent ending in an automatically created new one with zero content. So while DELETE is very dangeorous to use, UPDATE statements with still knowing exactly what you’re doing seem to be more safe.
For deleting (a lot of) orphaned statistics here’s a great how-to guide:
Also have run into this problem with suddenly incorrect data in database, now its solved after hunting wrong entry in Developer Tools → STATISTIC. But why this happens and how can we avoid it?
Why are not all entities available in Developer Tools → STATISTIC? For example, I have an entity with outdoor temperature, there are some errors and I can’t edit it
Due to a wrong cost of the energy price that I had I have manually deleted the cost data from statistics and statistics_short_term tables using the right metadata_id. Now the it started to populöate the tables with new data and correct cost. But I wonder if it is possible to re-generate the data for the past X number of days? How?
This topic is about fixing statistics data, not about discussing database structure observations. I’m very sure you’ll find another topic where this question is more suitable or even has been answered.
I don’t see a readme explaining on how to use it. Also no limitations documented - in the first lines I see you import MySQLdb, so is this script only working for MySQL databases? Etc. etc. Maybe you wand to add some more notes so this script can be more helpful to more people, understanding better on how to use it and for which setups.
Hello,
just as an info for others seeking solution for broken recorder statistics.
I did found dupondje homeassistant-fix-recordings script and improve it to fix recoder sum data (in the table statistics and statistic_short_term). It now supports SQLite as a default HomeAssistant database. I was able to use it to fix my broken statistics after modbus sensors went haywire.
