Migrate energy statistics from one entity to another

Configuration
7

@nbeernink I had a similar issue. I migrated from reading/storing the total solar production as 1 value to reading it individually from the different inverters we have.

As a result it would be very confusing to keep storing ‘old’ aggregated data under a completely wrong entity_id.

I made my new entities using the utility_meter integration, and then transfered the old statistics to these new entities using the SQL queries you mentionned above. Directly ediiting the database.
I used the SQLite browser, which worked really well. Downloads - DB Browser for SQLite

There was only 1 issue with the SQL query, when there are already entries using the same (new) in the database, the SQL query would throw an error.
The solution is to go from UPDATE statistics to UPDATE or REPLACE statistics

Here are the full SQL queries I used:

Statistics:

UPDATE or REPLACE statistics
SET    metadata_id = (SELECT id --Set the new entity name
                      FROM   statistics_meta
                      WHERE  statistic_id = 'sensor.total_tuinhuis_oost_solar_power_day') 
WHERE  metadata_id = (SELECT id -- Old entity name
                      FROM   statistics_meta
                      WHERE  statistic_id = 'sensor.total_solar_energy_day')

Short term statistics:

UPDATE or REPLACE statistics_short_term
SET    metadata_id = (SELECT id
                      FROM   statistics_meta
                      WHERE  statistic_id = 'sensor.total_tuinhuis_oost_solar_power_day')
WHERE  metadata_id = (SELECT id
                      FROM   statistics_meta
                      WHERE  statistic_id = 'sensor.total_solar_energy_day'')
12 Likes