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
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')
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)
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.
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.
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 ; -)
@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'')
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
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')
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.
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.
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]
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.
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;
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?
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.
