How to delete state values of a sensor that are too high (outliers)? (SQL command needed)

[linuxlurak]

Hi!

I want to delete wrong values of my sensor sensor.pm10 and sensor.pm25. Im using an SDS011 to measure the particular mater values in the air. Somehow (perhaps light that irritates the diode inside my SDS011?) there are many wrong data sets the are typically over a value of 5000 where a normal value would be aroung 1.0 to 300.

Can some one help me with the sql command?

I can delete all state values of my sensors with:
DELETE FROM states WHERE entity_id="sensor.pm25";
or
DELETE FROM states WHERE entity_id="sensor.pm10";

I’d like to delete only values that are grater than 1000 of the states table where entity_id is my sensor… Some one out there to help me?

Thanks a lot for your time!

[VDRainer]

Hi @linuxlurak,
DELETE FROM states WHERE entity_id="sensor.pm25" and state > 1000;
should do it.

[linuxlurak]

Many thanks @VDRainer! FYI and the sake of completeness: I’m struggling with my SDS011 particular matter sensor (Feinstaubsensor). I receive this graphs at the moment:

I opened an issue over at github (https://github.com/open-homeautomation/pmsensor/issues/6) but I’am not sure at all if it is the pmsensor library, my sensor or the way I feed the data to my home assistant (over socat). The strange thing about it is, that it’s only PM2.5 that’s wrong…

Perhaps I execute a cron job with a one liner like this:
/usr/bin/sqlite3 /home/pi/.homeassistant/home-assistant_v2.db 'DELETE FROM states WHERE entity_id="sensor.pm25" and state > 1000;'
Thanks again @VDRainer!

[linuxlurak]

hmm… I executed the command. for pm10 everything is ok now. but same command with pm25 deletes all data for pm25. I tested it two times and it cleaned up pm10 but deleted everything for pm25… I am completely lost… Is perhaps the database corrupted.

[VDRainer]

Well, according to your screenshots, the data for pm25 has only values above 1000.
So everything is deleted.
There’s no problem with the database.

[linuxlurak]

pm25 has regular values around 1 to 10 where the graph is down. In the upper right corner of the graph you can find the last reading. It was a normal reading.

I dumped the table to a cvs file. Here is an excerpt before the deletion of values:
state_id,domain,entity_id,state,attributes,event_id,last_changed,last_updated,created

2793413,sensor,sensor.pm25,3.6,“{”“unit_of_measurement”“: “”\u00b5g/m\u00b3"”, ““friendly_name””: ““PM2.5"”}”,“2017-07-17 17:26:04.892530”,“2017-07-17 17:26:04.892530”,“2017-07-17 17:46:18.045334”
2793504,sensor,sensor.pm25,4.7,“{”“unit_of_measurement”“: “”\u00b5g/m\u00b3"”, ““friendly_name””: ““PM2.5"”}”,“2017-07-17 17:30:12.511032”,“2017-07-17 17:30:12.511032”,“2017-07-17 17:46:27.535127”
2793538,sensor,sensor.pm25,4.6,“{”“unit_of_measurement”“: “”\u00b5g/m\u00b3"”, ““friendly_name””: ““PM2.5"”}”,“2017-07-17 17:31:44.921021”,“2017-07-17 17:31:44.921021”,“2017-07-17 17:46:31.464692”
2793554,sensor,sensor.pm25,4932.2,“{”“unit_of_measurement”“: “”\u00b5g/m\u00b3"”, ““friendly_name””: ““PM2.5"”}”,“2017-07-17 17:32:14.754209”,“2017-07-17 17:32:14.754209”,“2017-07-17 17:46:32.704804”
2793567,sensor,sensor.pm25,4.7,“{”“unit_of_measurement”“: “”\u00b5g/m\u00b3"”, ““friendly_name””: ““PM2.5"”}”,“2017-07-17 17:32:46.036464”,“2017-07-17 17:32:46.036464”,“2017-07-17 17:46:34.043847”
2793595,sensor,sensor.pm25,4.8,“{”“unit_of_measurement”“: “”\u00b5g/m\u00b3"”, ““friendly_name””: ““PM2.5"”}”,“2017-07-17 17:33:46.812790”,“2017-07-17 17:33:46.812790”,“2017-07-17 17:46:39.672120”
2793614,sensor,sensor.pm25,4.9,“{”“unit_of_measurement”“: “”\u00b5g/m\u00b3"”, ““friendly_name””: ““PM2.5"”}”,“2017-07-17 17:34:46.941113”,“2017-07-17 17:34:46.941113”,“2017-07-17 17:46:45.532491”
2793623,sensor,sensor.pm25,4.8,“{”“unit_of_measurement”“: “”\u00b5g/m\u00b3"”, ““friendly_name””: ““PM2.5"”}”,“2017-07-17 17:35:17.914048”,“2017-07-17 17:35:17.914048”,“2017-07-17 17:46:47.755266”
2793642,sensor,sensor.pm25,4.7,“{”“unit_of_measurement”“: “”\u00b5g/m\u00b3"”, ““friendly_name””: ““PM2.5"”}”,“2017-07-17 17:36:19.676214”,“2017-07-17 17:36:19.676214”,“2017-07-17 17:46:50.110411”
2793660,sensor,sensor.pm25,4.8,“{”“unit_of_measurement”“: “”\u00b5g/m\u00b3"”, ““friendly_name””: ““PM2.5"”}”,“2017-07-17 17:37:30.560952”,“2017-07-17 17:37:30.560952”,“2017-07-17 17:46:51.560329”
2793718,sensor,sensor.pm25,4.7,“{”“unit_of_measurement”“: “”\u00b5g/m\u00b3"”, ““friendly_name””: ““PM2.5"”}”,“2017-07-17 17:39:30.547340”,“2017-07-17 17:39:30.547340”,“2017-07-17 17:47:03.066321”
2793750,sensor,sensor.pm25,4.9,“{”“unit_of_measurement”“: “”\u00b5g/m\u00b3"”, ““friendly_name””: ““PM2.5"”}”,“2017-07-17 17:40:31.610762”,“2017-07-17 17:40:31.610762”,“2017-07-17 17:47:06.057523”
2793776,sensor,sensor.pm25,4.8,“{”“unit_of_measurement”“: “”\u00b5g/m\u00b3"”, ““friendly_name””: ““PM2.5"”}”,“2017-07-17 17:41:02.178825”,“2017-07-17 17:41:02.178825”,“2017-07-17 17:47:13.703326”
2793835,sensor,sensor.pm25,4932.2,“{”“unit_of_measurement”“: “”\u00b5g/m\u00b3"”, ““friendly_name””: ““PM2.5"”}”,“2017-07-17 17:42:36.605063”,“2017-07-17 17:42:36.605063”,“2017-07-17 17:47:28.135015”
2

I think I have to check the raw data of the sensor before it gets interpreted by pmsensor…

In the upper right corner of the graph you can find the last reading. It was a normal reading.

[linuxlurak]

I suspect that there is a problem with data type and deletion. The table has defined state as VARCHAR(255) affinity according to Pragma table_info states;.

sqlite> Pragma table_info(states);
0|state_id|INTEGER|1||1
1|domain|VARCHAR(64)|0||0
2|entity_id|VARCHAR(255)|0||0
3|state|VARCHAR(255)|0||0
4|attributes|TEXT|0||0
5|event_id|INTEGER|0||0
6|last_changed|DATETIME|0||0
7|last_updated|DATETIME|0||0
8|created|DATETIME|0||0

The proposed command does some comparison I don’t really understand in the end (I’m far a way of being a programmer or database admin or somthing like this ).

I have to cast the correct field type (or sqlish: affinity). This solution works for me:
DELETE FROM states WHERE entity_id="sensor.pm25" and CAST(state AS FLOAT) > 1000.0;

A possible cron entry would be:
/usr/bin/sqlite3 /home/pi/.homeassistant/home-assistant_v2.db 'DELETE FROM states WHERE entity_id="sensor.pm25" and CAST(state AS FLOAT) > 1000.0;'

[Paawan_Sharma]

Possible Solution to Garbage Value:
I also faced similar problem while using SDS011 with Raspberry pi 3 and python code. In my code I had to add a simple statement “ser.flushInput()” to flush the data before taking new sample value. Otherwise I was getting 4932.2 (which is not practical) for PM2.5.
This solved the ambiguity. Similarly, you can also look for flushing serial data in your programming language before getting new values.
Thanks
Paawan