You can use a tool like DB Browser for SQLite to modify Home Assistant’s database file.
Before editing home-assistant_v2.db
, I suggest you shutdown Home Assistant (so it doesn’t attempt to update the database while you’re tinkering with it) and make a backup copy just in case your modifications corrupt the database.
The table you want is called states
. Select the Browse Data tab then select Table: states
. Click the entity_id
heading to sort on it.
The best way to select and delete records is to use a SQL query. Select the Execute SQL tab and enter a valid SQL query string. To get you started here are a few examples. I’m using sensor.indoor_temperature
but you should change that to the entity_id of your temperature sensor.
This one lists all of the sensor’s records but only shows three of its fields. Take note of what you see in the attribute field.
SELECT entity_id, state, attributes FROM states WHERE entity_id='sensor.indoor_temperature';
This one does the same thing but limits the results only to the records whose attribute
field contains the string u00b0C
. That should be the code for °C
but confirm it with what you saw when you ran the previous query.
SELECT entity_id, state, attributes FROM states WHERE entity_id='sensor.indoor_temperature' AND attributes LIKE '%u00b0C%';
This query lists a sensor’s records where the state is unknown.
SELECT entity_id, state, attributes FROM states WHERE entity_id='sensor.outdoor_temperature' AND state='unknown';
After running the previous query and confirming it returns the correct results, I can change it into a command to delete those records from the database.
DELETE FROM states WHERE entity_id='sensor.outdoor_temperature' AND state='unknown';
In your case, the deletion query might look like the following one. Basically it’ll delete all records belonging to the sensor but only those whose attribute contains the string u00b0C
. Once again, make sure you change this string to match whatever you have that identifies the states that are in Celsius.
DELETE FROM states WHERE entity_id='sensor.indoor_temperature' AND attributes LIKE '%u00b0C%';
If you want to get fancy, instead of deleting these records containing Celsius values you could create a query to convert them to Fahrenheit (and change the string in the attribute). Of course, that’s more challenging than what I’ve shown above.
EDIT
Before exiting DB Browser, don’t forget to execute File > Write Changes to commit all modifications to the database file.