I have this issue where I changed the temperature reading mode on a device from C to F, and it screwed up the history graph. Any way to fix it or remove those points?
Strangely when I click to view the shorter term history graph, not “show more”, it looks fine. Though that might be because the smaller graph seems to show in 5 minute increments, and this change occurred in between the 5 minute window, so it might simply not be showing those points.
Ok I figured it out. I was able to manually go in and delete the data points using the “SQLite Web” Add-On. Make a backup if you are going to attempt this yourself.
Identified where the temperature data was stored and found the sensor’s metadata_id
Using the information from this article about Home Assistant’s database structure, I learned that Home Assistant might be storing the particular data points in one or more tables. I initially looked in the statistics and statistics_short_term tables, but my problematic readings were either not there yet or had been averaged out. Eventually, I found that the data was in the states table. More details on how below, but a big hint was that they showed up in the csv file I downloaded, which apparently downloads the states data, because in that csv the column with the data is titled states.
The devices are identified differently in the various tables. In the statistics and short term statistics db, my device had a metadata_id of 78, and in the states table it was 5002. I found this by looking at the statistics_meta table which stores the metadata_id of all the devices. I looked at the “content” tab of the statistics_meta table in the SQLite addon to directly look at the database data, and found the device by name, then looked at the metadata_id column. For the states database, the ids are stored in the states_meta.
By manually looking in states_meta, I identified that my sensor had the following details:
Note: This was after I already looked in the statistics and statistics_short_term and didn’t find it in there.
Queried the database to find the problematic readings
Since I switched from Celsius to Fahrenheit, I knew the incorrect readings would be much higher numbers. I ran this query to find them:
SELECT state_id, state, last_updated_ts, last_changed_ts
FROM "states"
WHERE metadata_id = 5002
AND CAST(state AS REAL) > 70
Found the values
The query returned 17 rows with temperatures around 76°F. These are the same values that appeared in the csv file I had downloaded from the GUI and posted a picture of in my original post.
After finding the specific state_ids of the incorrect readings, I ran:
DELETE FROM "states"
WHERE state_id IN (17423868, 17423879, 17423893, 17423905, 17423916, 17423925, 17423938, 17423950, 17423958, 17423975, 17423984, 17423997, 17424009, 17424019, 17424029, 17424043, 17424053)
That removed the rows from the database. I chose to explicitly list out the entries to delete, but you could alternatively use DELETE FROM with a similar query to the select one to automatically delete the ones with the bad values instead.
If you wanted to get really fancy, I imagine you could have done some kind of modification on the values instead of deleting them.
