Delete history of one sensor

[SgtBatten]

@frdmn

DELETE FROM states WHERE entity_id="sensor.cpu"

Shit is that what i just did too.

DELETE FROM states WHERE entity_id=“sensor.electricity_demand_max_monthly_1”

and all my history is gone for everything

[fokcuk (Serge)]

I had that happened- it caused by HA detecting DB corruption and recreating db file

[SgtBatten]

did you fix it or just left it to start fresh. If it’s an easy fix ill do it but otherwise ill live

[fokcuk (Serge)]

I tried but gave up

[axute]

I am using mariadb, for this I created the precedure:

CREATE OR REPLACE PROCEDURE delete_history (IN entityID VARCHAR(255))
BEGIN
    begin_finding: LOOP
        SET @z = NULL;
        select @z := s.state_id from states as s where s.entity_id=entityID and s.old_state_id  and s.state_id not in (select old_state_id from homeassistant.states as os where os.old_state_id is not null and os.entity_id= entityID ) ;
        If @z is NULL then
            LEAVE begin_finding;
        END IF;
        delete from states where entity_id=entityID and old_state_id  and state_id not in (select os.old_state_id from states os where os.old_state_id is not null and os.entity_id=entityID);
    END LOOP;
END;

call the script with sensor id:
CALL delete_history('sensor.stromzahlerstand');

[XereX]

This works fine. I had to add some extra using MySQL Workbench to do the job.

DELIMITER //
CREATE PROCEDURE delete_history (IN entityID VARCHAR(255))
BEGIN
    begin_finding: LOOP
        SET @z = null;
        select @z := s.state_id from states as s where s.entity_id=entityID and s.old_state_id  and s.state_id not in (select old_state_id from homeassistant.states as os where os.old_state_id is not null and os.entity_id= entityID );
        
        IF @z is NULL then
            LEAVE begin_finding;
        END IF;
        delete from states where entity_id=entityID and old_state_id  and state_id not in (select os.old_state_id from states os where os.old_state_id is not null and os.entity_id=entityID);
    END LOOP;
END //

Else it complained about syntax errors due to missing delimiter.

[salzrat (Michael)]

For anybody who finds this: deleting something from events will also delete the corresponding records from the state, as the state table is created with a “CASCADE DELETE” clause. Not sure why this error appears then…

UPDATE: after playing around in DB Browser for SQLite, the following seems the most compact way to delete an entity from the database, and you can customize it to your liking:

DELETE FROM events
WHERE event_id IN (
  SELECT a.event_id FROM states a
  WHERE entity_id LIKE '%myexpression%'
);

Due to the foreign key constraint, this automatically deletes all dependent states from the states table.

[salzrat (Michael)]

So I have a similar problem. I ran analyze-db.py, and it told me that “automation.automation_1”, an automation I created for testing a while ago and don’t need anymore, takes up over 90% of my events table.
But if I use purge_entities with this entity, nothing happens. At least for other entities I do get entries in the log file, but they are still there in the database.
I ended up just doing a
DELETE FROM events WHERE event_data LIKE '%automation.automation_1%'

Now I need to find a way to repack the database. there’s not enough diskspace…

UPDATE: analyze-db.py is not really a good way to analyze the database, as it ignores all events that don’t have an entity_id in their event_data (quite a few, and especially the state changes). The commands given here:

How to keep your recorder database size under control

Note: This guide was written for an earlier version of Home Assistant (around 2021). Since then, changes have happened, but this guide hasn’t been updated (yet). Please be aware your Home Assistant version can be slightly different, and thus you need to adapt yourself the instructions from this guide. Introduction This is yet another guide on: How to view the size of your database. How to figure out which entities are using too much space. And how to filter them out. I’m running Home Assi…

work much better, and you can easily modify them for ordering by byte size etc.

For large databases on underpowered systems, I found it more convenient to copy the database to my desktop (but stop homeassistant to avoid database corruption: ha core stop), and then work on it using DB Browser for sqlite. This also allows you to VACUUM the database (i.e., repack) even if you don’t have enough space on the homeassistant drive (you need the size of the database as additional free space).

I was able to shrink my database from 29GB to 13GB (I like to keep a long history, so this already has 6 months). The culprit were mostly the Unifi bandwidth and uptime trackers.

I then used the hints here:

Size of Virtualbox VM .vdi file and shrinking it

Hello all, my HA is running almost perfectly for many months now in Virtualbox. As many of you will know, the .vdi is ever increasing, especially when updates are incoming. My virtual disk is configured as 64Gb as SSD now, I already increased it from 32Gb. Just to give some numbers: -) my Host OS is Ubuntu 20.04 it shows me a size of 44.0 Gb now -) HA tells me 22% used 45.9Gb free (it obviously recognizes the full 64Gb is my guess) -) my home-assistant_v2.db is approx 3.6Gb large. My record…

to shrink the virtualbox image size (using fstrim).

[blackmore]

I had an issue with my energymeter sensor. So the sensor recorded a value of 0,81 kWh, but there ar 8100 kWh. I removed the false values from DB using the SQlite Plugin, in the historyview of the entity i don’t see the spike downwards anymore. But in the Energydashboard there is shown that i consumed in the hour i corrected the sensor ~8100kWh because the value changed from 0,81 to 8100.
Is there some mechanism to retrigger the calculation for energydashboard from history data? Or should i just wait? Is the history somewhere else saved or just in the states table?

Thanks for your help!

Intereseting is, that in the quickview of the entity there is still the spike:

but in the details there is no spike to see…

[velkhor]

I have similar problem, my DB(mariadb) have over 24GB.
when I run:
DELETE FROM states WHERE entity_id=“sensor.sun_elevation”

Cannot delete or update a parent row: a foreign key constraint fails

I think the problem is FOREIGN KEY “states_ibfk_2”. Can I remove it pernamently?

[hajo62 (Hajo)]

Why not use i.e. dbeaver and manually delete shown values?

[lzecca78 (Luca Zecca)]

The procedure seems ok, but when i try to execute it, i receive an error because of different table encoding:

#1267 - Illegal mix of collations (utf8mb4_unicode_ci,IMPLICIT) and (utf8mb4_general_ci,IMPLICIT) for operation '='

Indeed, some tables has utf8mb4_unicode_ci, others utf8mb4_general_ci. Is there any way to get rid of this? Thanx

[alexw2 (Alex W)]

For anyone coming here in 2024 and getting confused because all entity_ids are NULL, the database structure is different now and entity IDs are stored elsewhere. This thread has the details:

All entity_id NULL

Hi all, can someone please help me? I am not very experiance in SQL and I have round about 100SQL queries in Grafana and nothing works anymore. Can someone please translate this example to the new format? SELECT last_updated_ts AS "time", '°C' AS metric, CAST(state AS float) AS "InnenTemp" FROM states WHERE state <> 'unavailable' and state <> 'unknown' and entity_id = 'sensor.1w_ds2438t' ORDER BY last_updated_ts I never saw something like this, how is it possible to make so signi…

In my case, I’m using a standard SQLite backend and I have a “sensor.hot_tub_solar_mat_temp” sensor pulling values from a counterfeit DS18B20 that occasionally returns 0C i.e. 32F.

This query finds all rows with state equal to 32F that I want to delete.

SELECT *
FROM
  states
WHERE
  metadata_id = (
    SELECT
      metadata_id
    FROM
      states_meta
    WHERE
      entity_id = 'sensor.hot_tub_solar_mat_temp'
  )
AND
 states.state==32

Replace SELECT * with DELETE to purge them.

[nilsreiter (Nils)]

Did that snippet work for you @alexw2 ?

I had to change == to = in the last row, but when I execute for deletion, I get this error message:

Cannot delete or update a parent row: a foreign key constraint fails (homeassistant.states, CONSTRAINT states_ibfk_1 FOREIGN KEY (old_state_id) REFERENCES states (state_id))

[bsafh]

OK, that looks a bit more complicated.

What SQL statement would be needed not to delete entries for one specific entity_id, but all value for all entities macthing .e.g. "%sensor.sm_%" (in MariaDB)??

[KluthR (Robin Kluth)]

Same here. A UI clearing function would be awesome.

[MikeyDK]

Came here expecting there to be a way to do this, just like it was made possible to adjust utility meters.

[Themsk (Dragoș Custură)]

i have phpmyadmin addon installed and i use:

DELETE QUICK FROM `states` WHERE metadata_id IN (SELECT metadata_id FROM states_meta WHERE entity_id LIKE 'sensor.memory_free')