Yesterday it snowed all day, and my photovoltaic panels were completely covered with snow. Nevertheless, Home Assistant showed a PV yield of 4kwh around 07:30. After some hours of troubleshooting I was able to find the error this morning. For whatever reason, my power meter had reset the info interface to the default, which no longer provides the value in the desired resolution.
That was quickly resolved. But now there were incorrect state values in Home Assistant that needed to be corrected. And I want to share these steps with you how I corrected the values.
I’m assuming SQLite Web is installed and configured to access the home assistant database.
Since I knew the timing and entity of the error, finding the wrong values was quite easy. What I want to correct is the state and statistics, and we start the with the state:
The ID I was looking for is 1058. The next step is to look for the wrong counter readings. The actual counter value is 40917.2
As I scroll further, I suddenly found the outliers.
For the next step, the upper and lower State_IDs are needed:
- 63274444
- 62767537
All entries in between should be overwritten. In this case, that’s about 11 records.
After the update, a quick check of the states:
This looks good.
Now I apply the same process to the statistics – the SQL Statements are listed here for reference:
SELECT * FROM "statistics_meta" where statistic_id = "sensor.esp_heizungunten_pv_ertrag_gesamt"
SELECT * FROM "statistics" where metadata_id = 138 order by id desc
UPDATE "statistics"
set state = 40917.2
where
metadata_id = 138 and id > 927664 and id < 931627
SELECT * FROM "statistics_short_term" where metadata_id = 138 order by id desc
UPDATE "statistics_short_term"
set state = 40917.2
where
metadata_id = 138 and id > 11124473 and id < 11169297
And here is the result:
I hope this helps others in understanding how to update values in the Home Assistant Database.
Cheers
