Hi all, I use HA in a container so I don’t have access to add-ons but I think I need access to the home_assistant_v2 database to resolve this issue…
Not sure what happened but after a restart of HA incorrect data was being written to my history_stats entity that records the time/day that a heater is on. Heater was off but it was recording that it was on.
After a restart the issue cleared but there is some data in the recorder/LTS that I want to delete - but it is not as easy to do as I hoped.
Anyone have any advice / tips to get me started?
below is a screenshot of the data I need to purge/delete.
Developer Tools → Statistics. Find your entity and click the ramp icon to the right of it. Search for the time the erroneous data occurred and change it.
Been thinking about this a bit more and i use max / day statistics to do all my consumption calcs and charts. So there will be a blip on this day as the max for the day is incorrect in the LTS.
How do you edit/delete data for LTS? I have been reading that is possible to connect to the db as it is a SQLlite db but not sure how to get started (and i know this is risky!)
For anyone with a similar issue and if you are confident using sql queries. Be warned that if something goes wrong you can screw up the HA database SO ALWAYS CREATE A BACKUP and do the testing practice run on the backup database before doing anything to the live/prod db file.
install DB Browser for SQLite on the pc/mac that you will be using to do the work
stop/close HA and TAKE BACKUP/COPY of the HA database file from the config folder (home-assistant_v2)
use DB browser to open the backup/copy database
Now you have the HA database open, you can view the data in all the tables.
Browse data for the table statistics_meta has the mapping of statistic_id (name) to id. You need to note the id of the statistic you are interested in.
You can then run a simple SELECT SQL query to view the statistics data for the statistic.
SELECT * FROM statistics WHERE metadata_id = 40 AND start_ts > 1693836000 AND start_ts < 1693922400 AND min > 1;
For this example the id is 40 for the statistic and I have used start_ts to limit the query to the specific day and values the data started going wrong
It’s important that the select query is returning only the data you want to delete!!!
Once happy withe the select statement, you change it to DELETE FROM…
Execute the query and click the write query button.
Repeat process for the statistics_short_term table/data.
If you are OCD like me, then I also deleted the rubbish data from the states table but note that the id is different so you need to find the correct id in states_meta table.
Once data is deleted. Test the changes by running HA on the backup/copy that you have been working on.
ALWAYS KEEP A BACKUP OF YOUR database so you can get back to where you started from!!!
###BE CAREFUL THIS CAN MESS UP YOUR INSTALLATION DO THIS AT YOUR OWN RISK###
I did a plunge in the deep end of the pool and worked out from this post and post: @BeatleUKRecalculate statistics table sum to fix wrong energy data - #10 by BeatleUK
A (partial) solution. As my knowledge of DB was/is none here some additional steps I took:
Install Advanced SSH & Web Terminal from the add-ons, without it I could not gain Putty SSH access.
There are instructions on internet. Make sure to generate RSA Key. I used the Putty KeyGen tool, as on my W10 keygen command was not available.
Once setup and connect via a terminal you need to do a few steps before you can start.
First backup the ‘home-assistant_v2.db’
I used curl to FTP it to my NAS.
Then install SQLITE, check on:
Get the metadata_id for the sensors that you need to correct/delete values on. Took me a while to figure out why this was not working first couple of times. In the posts I read the sensor name was in double quotes it needs to be in single quote
SELECT * FROM "statistics_meta" WHERE "statistic_id" = 'sensor.waterverbruik_dit_kwartier';
Response is: 36|sensor.waterverbruik_dit_kwartier|recorder|L|0|1|
The first bit is the metadata_id.
Next bit that I needed to figure out was how to contain the SELECT query in the time frame the data was corrupted. I sow in posts this qualifier start_ts > and then a huge number. This appears to be UNIX time. To work out the UNIX time for my period I needed to correct I used:
Once you have your UNIX time stamp you can check the statistics and statistics_short_term tables for incorrect values. Note the metadata_id number is not in quotes.
SELECT * FROM "statistics" WHERE "metadata_id" = 36 AND start_ts > 1719860400 AND start_ts < 1719865800 AND min > 1;
SELECT * FROM "statistics_short_term" WHERE "metadata_id" = 36 AND start_ts > 1719975720;
To delete the incorrect entries you can either use the row id or time stamp. For both you can do exact time stamp or row id and delete a single value or use a range like example
DELETE FROM "statistics_short_term" WHERE "metadata_id" = 36 AND rowid > 7863537 AND rowid < 8045120;
DELETE FROM "statistics" WHERE 'metadata_id" = 36 AND start_ts > 1719860400.0 AND start_ts < 1719864020 AND min > 1;
When all values that need to be delete are gone exit the database
.exit
Now you need to start Home Assistant
ha core start
Once started exit terminal
exit
Check you in Home Assistant what you have done.
Then for me the magic started
When I check for some sensors I still had incorrect values although I deleted them all from the long and short term DB.
So I went back in the DB and saw that new high values where added, removed them again but still the same
When I delete the high values and check the DB then there is no high value in it anymore.
Has someone an idea what is going on and how to solve this?
