Delete erroneous data from database

Configuration
1

Occasionally one of my sensors sends bad data - it’s a wind speed sensor and it sends 1000 m/s.

I’ve been deleting the data using SQLite Web. Originally I did something like

DELETE FROM “states” WHERE entity_id=sensor.weather_wind_speed AND state > ‘100’;

This, obviously failed when the DB chanced to using metadata_id as the identifier. So then I did

DELETE FROM “states” WHERE metadata_id=‘76’ AND state > ‘100’ ;

I got the metadata_id by looking in the correct table. That failed recently because it looks like all the metadata_id changed. So now I do

SELECT * FROM “states_meta” where entity_id=“sensor.weather_wind_speed”

to get the metadata_id and then use that in the DELETE command.

I feel there must be a better way of doing this. Any suggestions?

Some background if it is relevant: the data comes in via MQTT with this config

  • name: “Weather Wind Speed”
    unique_id: weather_wind_speed
    state_topic: “enviro/weather”
    value_template: “{{ value_json.readings.wind_speed | float }}”
    device_class: “wind_speed”
    unit_of_measurement: ‘m/s’
    icon: “mdi:weather-windy”

My install is fully updated.

Thanks

2

I attacked this by joining the metadata table in the SQL query:

SELECT *
FROM `states` 
    LEFT OUTER JOIN `states_meta`
      ON states.metadata_id = states_meta.metadata_id
WHERE states_meta.entity_id = sensor.weather_wind_speed and states.state >100
1 Like
3

Minor comment for @Mikebert4 - the sensor.weather_wind_speed needs to be in quotes, otherwise the DB will try to search for table/column of the name…
So the correct one would look like this:

SELECT *
FROM `states` 
    LEFT OUTER JOIN `states_meta`
      ON states.metadata_id = states_meta.metadata_id
WHERE states_meta.entity_id = 'sensor.weather_wind_speed' and states.state >100