Migrate energy statistics from one entity to another

Configuration
21

this works for me:

:arrow_down: == Migrieren der Langzeitstatistik einer Entität:
UPDATE or REPLACE statistics SET metadata_id = (SELECT id FROM statistics_meta WHERE statistic_id = "sensor.NEUER_ENTITÄTSNAME") WHERE metadata_id = (SELECT id FROM statistics_meta WHERE statistic_id = "sensor.ALTER_ENTITÄTSNAME")

:zap: == Migrieren der Kurzzeitstatistik (Standard: 10 Tage):
UPDATE or REPLACE statistics_short_term SET metadata_id = (SELECT id FROM statistics_meta WHERE statistic_id = "sensor.NEUER_ENTITÄTSNAME") WHERE metadata_id = (SELECT id FROM statistics_meta WHERE statistic_id = "sensor.ALTER_ENTITÄTSNAME")

4 Likes
22

What I did was duplicate the data. I thought it was saver this way:
(note that I also had a unit difference kWh vs Wh, hence the *1000)

 INSERT INTO statistics  
    (metadata_id, state, sum, created_ts, start_ts)  
SELECT 792 as metadata_id, state*1000, sum*1000, created_ts, start_ts
FROM statistics  
WHERE metadata_id = 476 AND start_ts < 1726246800;

start_ts < 1726246800, because the new sensor had values from that number onward.

5 Likes
23

Interesting timing, I was just exploring to attemt the same idea, so big thanks for sharing.

Do you know if I also need to try and apply the same duplication in my influx db or is that not needed?
(I suspect that the energy stats are purely from statistics and not from influx)

And what about statistics_short_term? Did you do the same or also not needed?

24

This works nicely, even though I usually just select all the entities with their IDs from the meta table and then work with the IDs as it means much shorter queries. Result is the same thought, so to each his own.

What I wanted to add is, that almost always the new statistics are wrong as they start from zero, so you need to also fix the data, not just the IDs.

What I did today after HA decided to rename my _cost entites to _cost_2 was this:
UPDATE statistics SET state = state + (SELECT state FROM "statistics" WHERE metadata_id = 394 AND start_ts = 1735149600), sum = sum + (SELECT sum FROM "statistics" WHERE metadata_id = 394 AND start_ts = 1735149600) WHERE metadata_id = 394 AND start_ts > 1735149600

The start_ts value used is the value in the last entry of the old entity and first entry of the new entity, so this way you take the last state and sum values from the old entity and add them to all the newer entries of the new entity.

3 Likes
25

I would say not only you should do the same with short term stats, you should do it first because long term stats are generated from the short term ones.
It happened to me that I was so slow working on the fix, that newly fixed statistics got updated with wrong values from the unfixed short term stats…

26

Thanks! Switched successfully from Volkszaehler to Tasmota device.

27

It has been a while but this worked for me. Other examples involved deleting entities which wasn’t available for me as I wanted to replace only one channel of a 2 channel Shelly PM.

Migrating an entity’s long-term statistics

UPDATE or REPLACE statistics SET metadata_id = (SELECT id FROM statistics_meta WHERE statistic_id = "sensor.NEW_ENTITYNAME") WHERE metadata_id = (SELECT id FROM statistics_meta WHERE statistic_id = "sensor.OLD_ENTITYNAME")

Migrating short-term statistics

UPDATE or REPLACE statistics_short_term SET metadata_id = (SELECT id FROM statistics_meta WHERE statistic_id = "sensor.NEW_ENTITYNAME") WHERE metadata_id = (SELECT id FROM statistics_meta WHERE statistic_id = "sensor.OLD_ENTITYNAME")
5 Likes
28

Yes this migrates the historical data but it deletes the data in the old sensor. How can I achive to COPY the data from one sensor to another?

29

I assigned new data to id 999 (checking before if it’s not already in the db)
followed by old > new and 999 > old.

I managed to move the data between ids just fine, however, after few minutes, next entry in short_term stats gets added with value 0…

I delete all the new entries with:

DELETE FROM "statistics_short_term" where metadata_id = 689 and created_ts > 1754421210.5

And after few minutes same repeats.

Any ideas what am I doing wrong?

30

For a brief moment I thought that In ZigBee cluster management, the metering>summation delivered register holds the value. I wrote new value (big summation from old entity) but it got overwritten.

Something in the device firmware keeps track I’m afraid.

31

I want to substitute my current energy entity with a new one. What is the proper way in doing so that no data will be lost from anywhere either historical or current?