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:

3 Likes

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.

2 Likes

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.

1 Like

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

1 Like

@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'')
8 Likes

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:

SELECT *
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
4 Likes

For the next guy or gal, don’t forget to also migrate your cost statistics, otherwise the cost column remains empty.

The cost statistics_id is usually the same as the energy statistics ID but with a _cost suffix, e.g. sensor.energy_consumed_tariff_1_cost

2 Likes

I just had the same problem, too and also want to add, that it’s not just _cost but also _compensation for the entity that tracks energy sold to the grid.
In my example sensor.einspeisung_gesamt_compensation.

Old thread but still relevant for many.
I’ve changed from using fibaro hc2 to using a z-wave stick with z-wave js (ui). I removed devices from Fibaro and then added them in Z-wave JS UI, one by one. At the same time I cleaned up the entity IDs and names, and would love to migrate long term statistics.

I’ve tried using this query:

UPDATE statistics
SET    metadata_id = (SELECT id
                      FROM   statistics_meta
                      WHERE  statistic_id = 'sensor.rackskap_power')
WHERE  metadata_id = (SELECT id
                      FROM   statistics_meta
                      WHERE  statistic_id = 'sensor.basement_rackskap_111_power')

That returns the error:

UNIQUE constraint failed: statistics.metadata_id, statistics.start_ts

I assume this is because of a scheme change recently, or that since the table statistics is linked to statistics_metadata it fails because there already is a ‘sensor.rackskap_power’ in that table. Or something like that.

Any tips on how to achieve what I’m after?

I wouldn’t mind losing the newest data, as that is only for a few hours today, while the oldest data is for the last 12 months.

1 Like

I ran into the exact same issue when I replaced one MQTT source with another. I would like to change all the data points I’ve gathered over the years to the new entity.

However I cannot get this to work:

MariaDB [hassio_db]> UPDATE statistics SET metadata_id = 299 WHERE metadata_id = 48 AND id < 678088;
ERROR 1062 (23000): Duplicate entry '299-1682092800' for key 'ix_statistics_statistic_id_start_ts'

I run Mariadb for the long term statistics stuff and I cannot find a way to change these rows to assign them to the proper entity.
Does anyone have an idea of how to achieve this?

Alternatively I would add an entity that can act as a dummy device that’s just there to show the series in the energy dashboard, but that too seems to be pretty difficult.

Any update for this? :confused:

i searched for the first value of the new sensor and added the start_ts as another WHERE restriction like this:

UPDATE statistics
SET metadata_id = (SELECT id
                      FROM   statistics_meta
                      WHERE  statistic_id = 'sensor.new')
WHERE  metadata_id = (SELECT id
                      FROM   statistics_meta
                      WHERE  statistic_id = 'sensor.old') and start_ts < [number of the first entry of the new sensor]
4 Likes

Hi guys, after migration of statistics and statistics_short_term data for my weather station, after i switched from 3rd party integration to the core one, i dont see these old migrated data in history of new sensors. Do I need to do some kind of recalculation or something? I am using postgresql.
Thanks in advance for every advice.

I got it done, just needed to update metadata_id in states as well.

Recently did somehing similar for PostgreSQL.

  • get old id:
    SELECT id FROM "statistics_meta" WHERE "statistic_id" = 'sensor.old'
  • get new id:
    SELECT id FROM "statistics_meta" WHERE "statistic_id" = 'sensor.new'
  • export current data to CSV:
    COPY (SELECT created_ts,metadata_id,start_ts,state,sum FROM "statistics" WHERE "metadata_id" = <id.old>) TO '/tmp/export.csv' csv;
  • modify CSV:
    sed -i 's#,<id.old>,#,<id.new>,#' /tmp/export.csv
  • import:
    COPY statistics(created_ts,metadata_id,start_ts,state,sum) FROM '/tmp/export.csv' csv;
2 Likes

This looks as a very neat way to sort my issue with an unwanted name change of some sensors (from sensor.total_imported_energy_cost to sensor.total_imported_energy_cost_2)

How did this affect the different calculations and data shown in statistical cards?
I saw that I have a state and a sum column in the statistical database. The state is reset at certain intervals but the sum is not, it keeps increasing.

If I merge the 2 sensors by updating the metadata_id there will be a have a large drop in the sum column. Will this result in incorrect data if I look at the entire year (my sensoor changed about a month ago). Should I add the last sum of the old sensor to the sum in all rows for the new sensor?

Or am I missing something? Will this be updated automatically or is the sum column not used?

how would one migrate the DB from an old HA instance to a new one to retain all the historical data in all the graphs?

This is brilliant. Thank you all for giving me the confidence to try this. Below is what I did. I used the SQLite addon in Home Assistant.

First of all make a back up of Home Assistant.

Original Entity_ID (Solar energy) has over a year of history I want to keep.
name: Solar energy
Entity ID: sensor.shelly_em_channel_2_energy

Created a New Template sensor entity
name: Solar energy In
Entity ID: sensor.pv_energy
Template: {{ states(‘sensor.shelly_em_channel_2_energy’)|float }}

Now open the SQLite web view.
Read the metadata_id from the statistic_meta table (your numbers will be different), for your sensors.
id statistic_id
22 sensor.shelly_em_channel_2_energy
120 sensor.pv_energy

Queries just to test it out
SELECT * FROM “statistics” where metadata_id = 120
SELECT * FROM “statistics_short_term” where metadata_id = 120

Queries to change all the metadata_id of the old entity to the new one.
UPDATE or REPLACE statistics SET metadata_id = 120 WHERE metadata_id = 22
UPDATE or REPLACE statistics_short_term SET metadata_id = 120 WHERE metadata_id = 22

After this just use the new Entity name in the Energy dashboard.

Now I’m ready to tackle the change from Shelly to Victron in the coming month. I know the meter value will change so will have to make some sort of adjustment.

Rob

3 Likes

Thank you for this post, saved me from losing 2 years of energy statistics when migrating entities! I have a unit price entity with stats but the new version doesn’t seem to be in statistics_meta. Any idea where they might be? FYI I’m using this GitHub - megakid/ha_hildebrand_glow_ihd_mqtt: Home Assistant integration for local MQTT Hildebrand Glow IHD now but used to have manually defined mqtt entities.