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")

2 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.

3 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.

2 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.