One of my sensors returned some bad data, and those values are throwing off the vertical scale on the history graph, to the extent that the variations I’m interested in can’t be seen at all. The graph is of temperatures, and the bad data is -575F. So the difference between 68 and 72 is invisible.
Is there a way to remove those erroneous data points? The points are from a climate current_temperature attribute.
You can with sql quite easily.
I’ve read through the linked thread, and I see that you must be talking about the icon with the little ramp with an arrow on it. My statistics page only shows that icon for the energy summation lines.
Any other ideas? I might be willing to try doing it with sql, but would need to be pointed at a tutorial.
Here’s an example of how you can delete a bad reading with sql - in my case, it was an impossible value (65536) for the fan speed of my heat pump.
The sensor (sensor.heatpump_fan_cfm) doesn’t appear in the Statistics tab, so I had to go to the database itself. Here’s what I did (I’m running inside a Docker container, but I did the work outside the container).
> cd config/homeassistant # Go to the directory with my HA configuration, database, and logs.
> sudo sqlite3 home-assistant_v2.db # Open the database; it’s owned by root, hence the sudo
select * from states where state = 65535 and metadata_id = (select metadata_id from states_meta where entity_id = 'sensor.heatpump_fan_cfm'); # Make sure I’m getting the right entry to delete.
delete from states where state = 65535 and metadata_id = (select metadata_id from states_meta where entity_id = 'sensor.heatpump_fan_cfm'); # Do the deed
.quit # all done
The erroneous value was gone, and my history graphs were useful again.
Thank you! I’ve been away from the forum for a while, so I only just saw this.
anyone know why only some entities have the editing possibility in gui?
I looked for this solution very long! Thank you for this simple and well working method 
If i edit database values they do not show up in frontend, even if i commit or reload HA
I just ran into this problem and had to clean up David’s sql code above slightly.
I had a power meter on a freezer that was reporting a huge spike when the compressor kicked on, and sometimes would get a negative reading. I wanted to delete all these erroneous readings.
I am running HA in a docker container on a NAS, and ran these commands from telnet into the NAS directly. I modified the database while the docker container was running, and was able to just refresh the HomeAssistant website after to see the changes.
Hope this cleanup is helpful to those less used to SQL queries!
John Vickers
command: sudo su
command: cd /volume1/SmartHome/HomeAssistant/
command: sqlite3 home-assistant_v2.db
Note: These are not nessessary, but make the printout in the terminal human readable
command: .header on
command: .mode column
command: SELECT * FROM states WHERE (CAST(state AS FLOAT) < 0 OR CAST(state AS FLOAT) > 150) AND metadata_id = (SELECT metadata_id FROM states_meta WHERE entity_id = ‘sensor.freezer_power_meter’);
Note: the state field is a variant type, so often readings are stored as text. You have to use the CAST() function to turn them numeric for comparisons. I combined 2 comparisons with an OR in () to get all negative values as well as any reading over 150W.
command: DELETE FROM states WHERE (CAST(state AS FLOAT) < 0 OR CAST(state AS FLOAT) > 150) AND metadata_id = (SELECT metadata_id FROM states_meta WHERE entity_id = ‘sensor.freezer_power_meter’);
Note: Same command as the SELECT, just replace “SELECT *” with “DELETE” to remove the offending entries.
Thank you for the idea! This was a really easy way to get in and tidy up some sensor data where I was still getting the maths right. Not to mention handy to grab local copies of a few config and backup files.
