Rename entity_id first.
my_sensor to my_sensor_djdhskcidjd
Delete device and re add it.
What device?
Means - change “entity_id”?
What for?
Please elaborate)
They are saying if you rename the entity that you want to delete first. This will have the effect of changing the entity ID in the data base and all the LTH will be assoiciated with the renamed sensor. When you delete the sensor you will be leaving all its LTH orphaned under the renamed name. You are then free to create a new entity give it the old name and it will be clear of any LTH .
Another great idea that I will add to my post. THese are great!! thanks @nikito7 !! Ill give credit just FYI!!
1 Like
So these orphaned records will never be cleaned up automatically? Good to know
That’s not what I said.
I said the entities themselves that CREATE the data you’re talking about never go away automatically - and you don’t want them to.
The data you’re talking about in history goes away with purge and that happens 10 days after it’s stale by default. Use the info above to kill LTS for a given entity. If you change an entities class or even the unit of measurement you WILL be prompted to fix the data and one of the options is to delete it. So do the thing mentioned above to slam the LTS.
1 Like
Sorry I must have miunderstood. I would never assume an entity would be deleted. These are all good things to know. Thanks for helping out a newbie understand!! Seems everyone that uses this platform knows this stuff but there is no real documentation on it so it makes it so difficult to learn as all the knowledge is in users heads not in the documentation. This is why I am putting stuff I figure out on my blog because it needs to live somewhere!!
Assume you have this template sensor (which is created in UI & thus has some “unique_id”):
Then you “rename the entity” (i.e. “change it’s entity_id” in UI - which is only possible for entities with “unique_id”):
a new entry (“metadata_id”) will be added to “states_meta” table of DB for the new “entity_id”:
and then new entries will be added to “states” table for this new “metadata_id”:
states for “old entity_id”:
states for “new entity_id”:
Same about LTS.
Means - a history (& LTS) for that “renamed” entity becomes stored “anew”.
Assume you deleted that entity with “new entity_id”.
In “core.entity_registry” you will find this entry in “deleted entities”:
"config_entry_id":null,"created_at":"2024-12-08T20:25:56.560740+00:00","entity_id":"sensor.testing_ids_in_db_xxx","id":"0c6a1c8d5eef2dc0fe1d55a343a0fb2a","modified_at":"2024-12-09T02:01:37.488929+00:00","orphaned_timestamp":1733709697.4888752,"platform":"template","unique_id":"01JEKZ6FJ8WJ4D2ZRBZBEV0A6F"}
which is ONE entry - since you “merely renamed entity_id”.
Now create a new entity with same “old entity_id”.
And we got old history back:
with a gap in DB:
because “entity_id” is a key in DB.
Means:
- The whole point of easily changing “entity_id” in UI may easily cause loosing old history. Contrary, this helps to merge “old history for deleted entity” & “new history of new entity”.
- That mentioned proposal to “rename entity_id → delete entity → create new entity” will not work if you wish to “start anew”.
1 Like
I’m pretty sure the history data follows the entity_id.
If you want to replace a device and keep the history, you remove the old entity, install the new entity before restarting HA and give it the same entity_id.
The opposite of that, if you want to clear the history of an entity_id, just change the entity_id name I think and restart HA. The old history is gone. You can then change the name back if you like…
I do not think so.
If you change entity_id (& restart or not) - new entries will be added to DB for the new entity_id, and old records still be there (until they are purged like all other entries after purge_interval). See an example above.
3 Likes
If you rename a entiry and the name tha tyou rename it to is NEW it will add a new row the to the database table giving that entity a new metadata_id value… Every state that this entiry changes to will be recorded in the database under the entity’s metatdata_id. If you delete that entity the records are still there just what i call orphaned, IE they do not have an entity that is assoiciated with them any more. If you recreate the sensor with the EXACT same entity ID. It will see that entity ID already exists in the database and will not create a new record but insted will asume the metadata_id form the old sensor. And it will inhert any state changes assoiciated with that metadata_id. THis is the same for STATE changes and STATISTICS as they are two seperate tabels in the database.
So renaming away and then back will just inharit its old history, hence the issue I was facing when I renamend my utility meater back to its old name it kept all that crapy statistic history.
There are a few workarounds on here so I think this is a really good colletion of info for future users!
1 Like
I think there is good reason of using “metadata_id” as a “primary key” (I am not a DB expert - so may use incorrect words, so apologize).
The “states_meta” contains these entries:
Imagine that the 2nd “entity_id” belongs to the same entity (which has some “unique_id”):
– someone opened more-info for this entity;
– since the entity has “unique_id” - it is possible to change some properties (incl. “entity_id”);
– so “entity_id” was changed;
– this change was reflected in “core.entity_registry”;
– and a new “entity_id” was added into “states_meta” with SAME “metadata_id”:
Now the entity with NEW “entity_id” will have consistent history.
Of course there are pro & contra for this possible behaviour.
I tested:
Changing entity id in UI method, only work if we delete sensor after.
Because it’s all connected to internal unique_id.
So just delete sensor.
Sometimes it’s not easy to orphanate it…
Renaming entity_id may help or not.
Random name it’s to avoid reuse in future.
yeah that’s what we figured out, rename it to something random which will take the history along with it under that new entity ID then just delete it and leave the records orphaned they’ll get cleaned up over time.
1 Like
Sorry, see no logic here.
After these actions you will have in DB two sets of data of what you call “orphaned entity”, of course both will be deleted in purge interval. What is a purpose of this?
because sometimes you’re playing around and have a lot of stupid data in your database. then when you finally get it all working you want that old data all cleared up…
Your answer was to “why would I want to delete old history” question - but not to “why would I want to have 2 sets of data to be deleted” ))
I have no idea how we even got here haha. I just wanted to post how to delete statistical data for a utility meter. Better answers were given not sure what others are talking about still. The use I need is solved
This is for who don’t know want edit database.
Editing database it’s not hard after all.
metadata_id
SELECT * FROM "statistics_meta" WHERE statistic_id = 'sensor.eb3a_adc_addon_r'
387 sensor.eb3a_adc_addon_r
check for bad values
SELECT * FROM "statistics" WHERE metadata_id = 387 AND min < 100
remove it
DELETE FROM "statistics" WHERE metadata_id = 387 AND min < 100
state_class: total_increasing. can be fixed from UI in dev tools.
state_class: measurement. only editing DB.
Delete things it’s the last resort to fix wrong values.
In the begin I just delete all DB. 
1 Like
yours is great for a few bad reading, if you want to go full scorched earth. I am in the process of writing a SQL Query to fix these bad records… just subtract the bad value reading from the state in all 3 tables state, statistics, statistics_short_term
1 Like
In this thread it is claimed that the current behaviour is a bug:
after changing “entity_id” for an entity with “unique_id” a history for this entity should be retained.
Currently it is not retained according to my test.