Hi,
In 2023.12 things changed: https://github.com/home-assistant/core/pull/104493
Unfortunately I had to change my sensor in the Energy Dashboard, but now I’ve lost my history:
The one on top is the old sensor, which isn’t updated anymore. The 3rd is the new one, starting from zero.
Is there a way I can migrate my old statistics to the new one? Now I’ve lost a year of data.
Big problem with HomeWizard. The upgrade disabled the t1 import entities and generated a new entity without history. So I was missing complete history. Swapping statistics id’s in de database resolved this. But utility meters based on the T1 entities also broken. Cost me already 3 hours to correct things in the database and statistics. Had to correct 17 devices…
What I noticed is that it generated dutch names i.s.o. english ones. And based on the device name. So maybe language setting related? For some devices which have been renamed in the passed I have half the entities with the previous name, half the new name, half in english and half in dutch. A mess!
My solution in the database, example for the wasmachine:
Get the ids:
select * from "statistics_meta" where statistic_id like "%was%import%"
Result:
435 sensor.wasmachine_total_power_import_t1
1196 sensor.wasmachine_totale_energie_import
3 sql commands to swap the ids
update "statistics_meta" set statistic_id="sensor.wasmachine_total_power_import_t1_old" where id = 1196
update "statistics_meta" set statistic_id="sensor.wasmachine_totale_energie_import" where id = 435
update "statistics_meta" set statistic_id="sensor.wasmachine_total_power_import_t1" where id = 1196
I did the same for states meta:
SELECT * FROM "states_meta" where entity_id like "%was%import%"
Result:
252 sensor.wasmachine_total_power_import_t1
4592 sensor.wasmachine_totale_energie_import
3 sql commands to swap the ids
update "states_meta" set entity_id="sensor.wasmachine_totale_energie_import_old" where metadata_id = 4592
update "states_meta" set entity_id="sensor.wasmachine_totale_energie_import" where metadata_id = 252
update "states_meta" set entity_id="sensor.wasmachine_total_power_import_t1" where metadata_id = 4592
Oh, and restart Home Assistant afterwards. It caches the ids…
Yep, was the same way I wanted to do it and fixed now :-).
Done creating the issue.
Thanks for the solution!
A breaking change just for "cleanup’, good one. Lost 14 months of history 
Good to read that someone found a solution. I found the codes. Where can I give the commands? I’m using MariaDB10 on a synology nas.
Found it, it was on a tab called SQL.
Thanks for the solution!
Hi @Frank_Howell and @pbootsma I don’t mean to derail your thread, but I’m asking for your help, I seem to have encountered a strange issue related to my HomeWizard SDM230 entity statistics, and I’m not sure how to resolve it exactly, but I think I’m getting closer.
I get that there is no obligation for anything, I’m just asking for help if anyone has time.
I opened a thread here to try explain the issue:
!!! Later Edit: This post initally described an extra SQL script to fix the sum column in the statistics table. That did not work for me as, after little time, HA continued to write wrong data to that column and perpetuated the issue. I have corrected the post to show my current fix which is to make minor changes to the DB (step 5) and fix the statistics from the Developer Tools (Step 7). I am still monitoring the health of my HA instance and looking our for DB issues.
!!!
I took another approach to fixing the problem. For me the issue appeared in March 2024, when i noticed i had lost my long term history.
Here are my steps:
1. Make backup. You can corrupt your DB and then you loose everything. Make sure you can recover.
2. Stop your HA instance. This way you minimize the chance to corrupt your DB.
3. Use DB Browser for Sql Lite to open your DB file.
4. Use Browse data, go to the statistics_meta table. Identify your old and new sensors and make note of their id’s. Do the same for the gas sensor.
Match these accordingly. For example in my case:
OLD id >>>> NEW id
2 127
3 128
4 130
5 131
8 134
The next steps will attempt to join the data that is already in your DB. What i am tryin gto do here is to use the old data associated with the old metadata_id and make it look like it is data belonging to the new metadata_id of the new sensor.
This causes an issue with the sum column. Once the data is “joined” the data in the sum colum goes up constantly until the moment when the new sensor gets added. Then it goes to 0 and start going up again. I try to fix this in step 7.
5. Update the old metadata_id in the statistics table with the new corresponding ID. In my case i used this script:
update "statistics" set metadata_id=127 where metadata_id = 2;
update "statistics" set metadata_id=128 where metadata_id = 3;
update "statistics" set metadata_id=130 where metadata_id = 4;
update "statistics" set metadata_id=131 where metadata_id = 5;
update "statistics" set metadata_id=134 where metadata_id = 8;
Here you might get an error is there is an overlap constraint in the timestamps. I removed the very last entry from the old data. So, for example metadata_id=2 would not overlap with it’s corresponding new metadata_id=127. Just filter for the medatada_id field, scroll all the way down and check the last entry. You should not have an overlap with the first entry timestamp of your new metadata_id. Repeat the removal of the overlap entries for each ID, and rerun your script.
After running this script, there should be no more old medatada_id’s in your statistics table.
6. Use the write changes button in your DB Browser for sql lite app to write to the file. Start HA.
7. Fix the sum column
Unfortunately i was not done. The sum column, has also reset for me so my statistics were broken. What i mean by this is that for the old values the sum continues to rise constantly and as soon as the new values from the new sensors appear, the sum goes to 0 and continues to rise from there.
I initially tried to run a SQL script to fix the sum column but HA did not like that and it continued to perpetuate the issue from the sum column.
My current solution is not to make any more changes from the DB except the ones described above. Just start HA, then go to Developer Tools > Statistics. Find your new sensor and clock the graph buton.
You will see exactly one value that is waaay off. In my case, for the gas sensor, i had a negative value from around the time when my issues started.
Click on it and adjust it to 0
Check statistics and confirm graph is looking nicely.
8. Monitor
You have made manual changes in the DB. Make sure your HA remains healthy. Watch out for exceptions in the logs related to DB issues. If you see funny stuff, you might be better off restoring the DB from backup and just writing off the lost data.
