How to update incorrect recorded senor state

Configuration
1

Hi,

I have a Sonoff temp/humidity sensor that threw a wobbly when the battery was changed and read -327.7 Degrees C and the humidity was off as well.
I have managed to fix the value in the states table with (I run HA as a VM, so note the path):

sqlite3 /mnt/data/supervisor/homeassistant/home-assistant_v2.db "UPDATE states SET state=18 WHERE state='-327.7' and metadata_id=112"

I have verified that the values have been corrected, but my graphs are still broken.
I have also verified that because the sensor uses state values, that it’s data is not in any of the statistics tables.
I have restarted and rebooted HA and confirm that the value is still fixed.
I’m on the latest updates, any thoughts on how I can fix it?

image

2

Click the “Show more” link in that card. This will show the state data that you have (possibly) corrected in the History Panel.

The pop-up card shows 5 minute statistics, not state data.

So if your state data is correct then you need to fix the data in the statistics tables as well.

3

Ah excellent, yes the “show more” does show the corrected data and I have poked in the statistics_meta table to find the corresponding metadata_id for the statistics table.
Weird that they would not be the same. Pitty these don’t pull through to the developer statistics page where you can fix it via the GUI.

Anyway for anyone looking to fix this in the later releases that has the longer term stats:

To fix that “states” values we need the metadata_id number:
sqlite3 home-assistant_v2.db "SELECT * FROM states_meta WHERE entity_id LIKE '%YOUR_SENSOR%'
YOUR_SENSOR=The name of the senor you are looking for

Next you need to find incorrect entries:
sqlite3 home-assistant_v2.db "SELECT state_id, state FROM states WHERE metadata_id=1234 and state>4321
1234=The metadata_id value from the previous step
4321=The “faulty” value recorded, you can either set the operator to >, < or = depending on what you are looking for.

To fix the incorrect values you can either update all the entries where the state matches your query:
sqlite3 home-assistant_v2.db "UPDATE states set state=1111 WHERE metadata_id=1234 and state>4321"
Or you can update individual entries by referring to the state_id and metadata_id:
sqlite3 home-assistant_v2.db "UPDATE states set state=1111 WHERE metadata_id=1234 and state_id=123123"

For the statistics it works exactly the same, but the metadata_id is located in statistics_meta:
sqlite3 home-assistant_v2.db "SELECT * FROM statistics_meta WHERE entity_id LIKE '%YOUR_SENSOR%'
And there are 2 tables to update for the stats, these are statistics and statistics_short_term

WARNING: I would suggest you do take a backup before you start or make a copy of your home-assistant_v2.db, if you run the UPDATE sqlite3 call without properly setting your WHERE clause correctly then you can update the incorrect entries or all entries and there is no undo :slight_smile:

1 Like