Migrate energy statistics from one entity to another

Recently I’ve migrated my P1 DMSR sensor firmware from an ESPeasy firmware that was being read via the DSMR integration to ESPHome DMSR. This however changed the entity ID’s and unfortunately the energy info is now being recorded into different entities.

old => new
sensor.energy_consumption_tarif_1 => sensor.energy_consumed_tariff_1
sensor.energy_consumption_tarif_2 => sensor.energy_consumed_tariff_2
sensor.energy_production_tarif_1 => sensor.energy_produced_tariff_1
sensor.energy_production_tarif_2 => sensor.energy_produced_tariff_2

The goal is to migrate energy history tracked on these 4 previous entities to the 4 new entities and then remove the old integration.

My plan is to:
0. Make a backup of home-assistant_v2.db

  1. Execute SQL queries via the SQLite Web addon:
UPDATE statistics
SET    metadata_id = (SELECT id
                      FROM   statistics_meta
                      WHERE  statistic_id = 'sensor.energy_consumption_tarif_1')
WHERE  metadata_id = (SELECT id
                      FROM   statistics_meta
                      WHERE  statistic_id = 'sensor.energy_consumed_tariff_1')
  1. Do the same for short_term statistics:
UPDATE statistics_short_term
SET    metadata_id = (SELECT id
                      FROM   statistics_meta
                      WHERE  statistic_id = 'sensor.energy_consumption_tarif_1')
WHERE  metadata_id = (SELECT id
                      FROM   statistics_meta
                      WHERE  statistic_id = 'sensor.energy_consumed_tariff_1')

(doing the same for all 4 entities)

  1. Remove the old integration (assuming the entities and history are deleted along with it)

Does this plan make sense?

I’m guessing this does not get rid of the big hump of data seen below that was recorded when the new entities were added? (which basically also contains last year as well). I will probably have to come up with queries that delete this data.

Starting over is also an option, but one I’m trying to avoid. :slight_smile:

Assuming both the integrations use discovery:

You could just remove the old integration, restart, delete the old “restored” entities if they exist (Configuration / Devices & Services / Entities) and rename the new sensors to match the old ones.

The old data will be retained and the newly renamed sensors will continue recording from where the old ones left off.

Make a full backup first (including with a stopped database to prevent corruption), just in case something goes wrong. It shouldn’t. I’ve done this before. But better to be safe than sorry.

Thanks for the reply Tom, I suppose switching to the new entity name would then be a hassle?

Yep. No idea how to do that and retain the data.

Just did you what you suggested. But considering I waited a week to ask this question, the data from the newly renamed entities is lost. The rest is matched up like you said. Oh well. :slight_smile:

The energy usage for the missing days is added up as usage for today and I suspect tomorrow will look normal again.

I just did something like this. The id of my solar production total changed , so I had no more long term statistics.
No need to use a select to find metadata_id’s, just look them up and enter the numbers in the update statement.
UPDATE statistics SET metadata_id = (new id) WHERE metadata_id = (old_id) AND id < (id of oldest entry with new id already in the database) ;
UPDATE statistics SET statistics.sum = state - (state of oldest entry) WHERE metadata_id = (new id) AND id >= (id where sum is zero )

You need to do the same with statistics_short_term

You see, there were 2 problems if HA had been running for a few days with the new sensor id. Without excluding the new entries you get duplicate index entries in the index that is created on the start .
Secondly, if it is a measure that increases , the statistic subtracts the oldest value from the state - logically so because it is like a meter that says 12000 when you install is, so that is to be subtracted.

I use mariadb and heidisql under windows, this is a good gui for messing with databases ; -)

@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:


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

Just wanted to share that I used this to migrate from old sensor names to new ones as I was working on my system this morning changing around how things report (splitting up to-grid and from-grid numbers from a new ESPHome-flashed Emporia Vue2)
It worked great!
I ended up needing to tweak a couple of mid-day statistics sum values that had been recorded between when I changed the names of the sensors and when I updated the previous statistics so it could pick up where the previous stats left off instead of reading it as resetting to zero, but that was easily accomplished by using some select and update calls, for example:
this pulled up all the sensor values:

FROM "statistics_short_term" WHERE  metadata_id = (SELECT id
                      FROM   statistics_meta
                      WHERE  statistic_id = 'sensor.house_energy_from_grid')

and this let me update all the ones after the reset, to add the amount from just before the reset:

UPDATE "statistics_short_term" 
          SET sum = sum + 35265
          WHERE  metadata_id = (SELECT id
                      FROM   statistics_meta
                      WHERE  statistic_id = 'sensor.house_energy_from_grid')
          AND id > 8902911