How to clear the long term stats of a sensor

Configuration
1

I was having a heck of a time trying to delete the long term stats (LTS) data for a sensor. I thought by deleting the sensor it would also delete the LTS but ithe data stayed persistent. Keep in mind this is because f you create the sensor again and it give it the same entity ID it will pull that entity ID’s history from the database. this is kind of a really cool feature if you don’t want to delete your LTS, but you want to recreate the sensor. if you do want to delete the sensor and its LTS this was really annoying as I had a very bad reading on one of my Heater Sensors that was a Utility Meter. I was using this meter to figure out how much it was costing us in the energy dashboard. I saw a spike of 1300kWh in a short period of time and then on the energy dashboard it was shown outrageous amount in the cost field. Editing this sensor using the developer > statistics tools it did not seem to work properly. all the statistics reported in there other than one were correct. I corrected the one wrong read but the history kept showing 1300+ kWh used. So after looking all over the internet with no real answers I decided to dive into the database and found a way to manually remove the data and wanted to share incase others wanted to do the same!

I would love to hear some feedback if I missed anything or if anything was not clear!

PS obligatory warning about editing databases if you don’t know what you’re doing. BACK UP FIRST! you have been warned.

2

The SIMPLEST ways to purge LTS for some sensor without a risk to break smth in DB:

  1. Delete this sensor (or may be just disable, have not checked this way). Go to Dev tools → Statistics, it will show you an “issue” about “LTS is stored but the sensor has no state” & it will propose you to delete old LTS. Delete it - then restore the sensor.
  2. Change “state_class” for this sensor: if this is a template sensor - comment the “state_class” line, for other sensors - set “state_class: none” via “customize” in yaml. Reload this sensor (depends on it’s integration), then go to Dev tools → Statistics, you will see an issue “LTS is stored but state_class is none” → similar to pt. 1 above.
2 Likes
3

Thank you so much, this is obviously a way better solution than mine. Problem was looking for this all over the internet I could not find this information. I do not recommend anybody edit the HA database unless they know what they’re doing obviously.

The real solution to this should be the HA devs need to add an option when deleting a sensor to ask “Would you like to also like to delete the LTH for this sensor?” and give a quick info message that if you don’t delete the LTH you can create a sensor again with the same entity ID and it will associate the old LTH with the new sensor.

I will update my blog posting to reflect both ways. I’ll do some testing to see maybe you can just rename the entity ID, then delete the sensor so the orphaned records are under a different entity ID. then create the sensor again.

These are the little things that everyone seems to know and understand but no one tells you there’s no documentation to explain. I could have just created a new sensor with a different name but I like the name I gave it haha

4

If an entity is deleted - a “normal” history (not LTS) is not deleted from HA, it just is not updated (and will gone completely after “purge interval”).
If you add an entity with same entity_id - you will see a current history, then a gap, then an old history (within “purge interval”).

5

sorry just so I’m understanding you correctly is there an automated what I’m going to call garbage collection that runs every night to delete old entries in the database that are orphaned? or is this something I need to run set up to run

6

By default, every day history in DB is purged (old data are removed). It is probably described in docs for Recorder.
It is not about “orphaned entities” - it is about all entities which are not excluded from Recorder.

7

like I do know maintenance is performed and instead of keeping thousands of records for a sensor it will merge all those into a single reading. that way it replaces a thousand entries with just one but these are normally older sensor readings not more recent sensor readings. am I understanding that correctly.

are orphan entries ever cleaned up by this automated system?

8

Irrelevant data for nonexistent entities yes. Orphaned entities that have somehow been broken off thier host integration. No.

Purge default is 10 days.

1 Like
9

Rename entity_id first.

my_sensor to my_sensor_djdhskcidjd

Delete device and re add it.

10

What device?

Means - change “entity_id”?
What for?
Please elaborate)

11

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
12

So these orphaned records will never be cleaned up automatically? Good to know

13

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
14

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!!

15

Assume you have this template sensor (which is created in UI & thus has some “unique_id”):
image

Then you “rename the entity” (i.e. “change it’s entity_id” in UI - which is only possible for entities with “unique_id”):
image

a new entry (“metadata_id”) will be added to “states_meta” table of DB for the new “entity_id”:
image

and then new entries will be added to “states” table for this new “metadata_id”:
states for “old entity_id”:
image

states for “new entity_id”:
image

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

with a gap in DB:
image

because “entity_id” is a key in DB.

Means:

  1. 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”.
  2. That mentioned proposal to “rename entity_id → delete entity → create new entity” will not work if you wish to “start anew”.
1 Like
16

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…

17

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
18

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
19

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

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”:
image

Now the entity with NEW “entity_id” will have consistent history.
Of course there are pro & contra for this possible behaviour.

20

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.