HomeWizard T1 entities and 2023.12 update, how to transfer energy metrics?

plonka2000 · December 29, 2023, 1:24am

Hi all,
I’m having a bit of a panic before upgrading to 2023.12.x, I’m currently on 2023.11.3 (Successfully upgraded from 2023.6.3 without issues).

I use 2x HomeWizard HomeWizard SDM230 units to measure my GRID and SOLAR usage.

Anyway…

Whenever there is an update, I pay special attention to “Breaking Changes”, now renamed to “Backwards-incompatible changes”, so I went through every update since 2023.6 and found this:

Unfortunately in May of 2023, I had solar panels installed on my house and through some bad installation issues (that were resolved quickly) the solar installers broke my HomeWizard SDM230 GRID and I had to replace it with a new unit.

After installing the new unit, Home Assistant recognised it as a new Meter, and created new entities and history for it. Because my old SDM230 has metrics from November 2022 until May 2023 that I cannot lose, in the end I left the old entity and renamed it “OLD GRID” and set it alongside the new “GRID” device in configuration:

Everything has been operating pretty well, but in the screenshot you can see that it is selected as the “import T1” and “export T1” entities. I did this because, for some reason, the “non-T1” entites show no metrics on my energy dashboard.

With “import/export T1” selected as in above screenshot:

With “Non-T1 import/export” selected:

Now the problem I have is that I think:

The HA 2023.12 update will remove the “import/export T1” entities.
My HA does not have complete data for “Non-T1 import/export”, I might as well not use that data, its dead.
I need to transfer the metrics data from “OLD GRID import/export T1” to “GRID non-T1 import/export” so I dont lose that metric data.
Then after that I assume I can upgrade, and lose the “T1 import/export” metrics without issues?
I assume I’ll need to edit the SQLite DB directly?

I am not sure where to start with this, I’m using SQLite on my instance.

Can anyone please help me with figuring out what I need to do?

plonka2000 · December 30, 2023, 6:24pm

I’m hoping someone with knowledge or experience of the DB schema might be able to help.
I’ve been using DB Browser for SQLite to browse and query the DB file, and I’m trying to track down the structure of the statistics.

I think I should need to copy and overwrite all the data from “sensor.HWWiFiMeter1 OLD GRID Total power import T1” to “sensor.HWWiFiMeter1 OLD GRID Total power import”

And also do the same copy and overwrite from “sensor.HWWiFiMeter1 OLD GRID Total power export T1” to “sensor.HWWiFiMeter1 OLD GRID Total power export”

I’m continuing to look through, but I don’t have much experience with the DB schema layout…

plonka2000 · December 30, 2023, 6:39pm

I saw in another thread that they were able to swap the statistics ids directly in the DB, and I’m looking to see if this might be a good option.

Maybe I can do this, but I have no idea if there are pitfalls for successfully doing this?
Will it break any internal references or such?

plonka2000 · December 31, 2023, 2:10am

So, without understanding the DB schema I went ahead and looked around, and figured I would try swapping the sensor IDs as described in another thread.

I’ve been poking around in the schema a bit to see what I can make sense of, and saw the references in “statistics_meta” and “states_meta” as described.

So I queried the ids:

select * from  "statistics_meta" where statistic_id like "%meter1_old%import%"

117     sensor.hwwifimeter1_old_total_power_import_t1	recorder	kWh	0	1	
137     sensor.hwwifimeter1_old_total_power_import	recorder	kWh	0	1

Updated the titles:

update "statistics_meta" set statistic_id="sensor.hwwifimeter1_old_total_power_import_t1_old" where id = 137
update "statistics_meta" set statistic_id="sensor.hwwifimeter1_old_total_power_import" where id = 117
update "statistics_meta" set statistic_id="sensor.hwwifimeter1_old_total_power_import_t1" where id = 137

I also did the same with “export”.

And queried the “states_meta” table:

SELECT * FROM "states_meta" where entity_id like "%meter1_old%import%"

85      sensor.hwwifimeter1_old_total_power_import_t1
456     sensor.hwwifimeter1_old_total_power_import
1621    sensor.hwwifimeter1_old_total_power_import_cost

And executed:

update "states_meta" set entity_id="sensor.hwwifimeter1_old_total_power_import_old" where metadata_id = 456
update "states_meta" set entity_id="sensor.hwwifimeter1_old_total_power_import" where metadata_id = 85
update "states_meta" set entity_id="sensor.hwwifimeter1_old_total_power_import_t1" where metadata_id = 456

And did the same for "export as well.

After restarting HA and updating the Energy Dashboard configuration references.

And everything is still the same, no change.

So with more poking around I can see that the “statistics” and “states” tables have field references to “metadata_id”. Does this attach the statistics/state entry to the entity?

I’m not sure, but everything still works like I did nothing.
I checked the ids manually and they are updated.

Is anyone with a better idea or experience have any pointers on the DB schema?

plonka2000 · December 31, 2023, 3:28am

Ok, I broke everything, because 1) I forgot to write my changes back to the DB and 2) I wrote my changes to the DB with HA running so 3) I guess that corrupted the DB.

That appears to break everything, because upon restarting HA all metrics are missing and I had to restore the backup I made, losing a few hours of data, but no big deal.

So after doing this a couple times, and realising my dumb mistake, I stopped HA and wrote my changes then started HA.

That seems to have done it and everything works.
The statistics are also swapped, so referencing the “HWWiFiMeter1 OLD GRID Total power import/export” now works in “Non-T1” and shows all metrics/statistics correctly in the Dashboard.

Looks like it worked.

So here is the complete SQL that I executed:

update "statistics_meta" set statistic_id="sensor.hwwifimeter1_old_total_power_import_t1_old" where id = 137;
update "statistics_meta" set statistic_id="sensor.hwwifimeter1_old_total_power_import" where id = 117;
update "statistics_meta" set statistic_id="sensor.hwwifimeter1_old_total_power_import_t1" where id = 137;

update "statistics_meta" set statistic_id="sensor.hwwifimeter1_old_total_power_export_t1_old" where id = 151;
update "statistics_meta" set statistic_id="sensor.hwwifimeter1_old_total_power_export" where id = 150;
update "statistics_meta" set statistic_id="sensor.hwwifimeter1_old_total_power_export_t1" where id = 151;

update "states_meta" set entity_id="sensor.hwwifimeter1_old_total_power_import_old" where metadata_id = 456;
update "states_meta" set entity_id="sensor.hwwifimeter1_old_total_power_import" where metadata_id = 85;
update "states_meta" set entity_id="sensor.hwwifimeter1_old_total_power_import_t1" where metadata_id = 456;

update "states_meta" set entity_id="sensor.hwwifimeter1_old_total_power_export_old" where metadata_id = 1473;
update "states_meta" set entity_id="sensor.hwwifimeter1_old_total_power_export" where metadata_id = 1472;
update "states_meta" set entity_id="sensor.hwwifimeter1_old_total_power_export_t1" where metadata_id = 1473;

update "states_meta" set entity_id="sensor.hwwifimeter1_old_total_power_export_cost" where metadata_id = 1623;

Maybe this will help someone else some day.