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

1 Like
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.

1 Like
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?