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. 
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'')
10 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? 
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;
3 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
4 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.
this works for me:
== 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")
== 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
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
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?