History-graph shows old unit of measurement

Configuration Frontend
1

If I add a new sensor, in this case a Zigbee Visonic door/temperature sensor, it gets detected and starts recording data. But it starts recording in degrees C. So, even though I’ve configured it now to F, the history-graph card thinks it’s still in C.

As I learned from a similar problem (History-graph shows different styles), my other thermometers show up on one graph, and, in the same card, the new one shows up on a separate graph, because it still thinks it’s in a different unit of measurement.

The simple solution is to delete the home-assistant_v2.db file. Now all the thermometers show on the same graph, because they’re all in “F”.

But there’s got to be a better way. I assume somewhere in the database it stores the unit of measurement of the first value (state change) and uses that. If that’s true, I’d like to delete some number of oldest (or even all) state changes for this one entity.

Or is there some other solution?

2

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.

2 Likes
3

I believe these two SQL statements will handle the task of converting the Celsius values into Fahrenheit. The first one performs the temperature conversion and the second one updates the unit_of_measurement in attributes.

This statement converts the value in state from Celsius to Fahrenheit.

UPDATE states SET state=(state*1.8)+32 WHERE entity_id='sensor.indoor_temperature' AND attributes LIKE '%u00b0C%';

This statement replaces the entire contents of attributes in order to change C to F. Maybe there’s a more surgical way to do it but this works too. Be sure to modify the entire attributes string to match your sensor’s value.

UPDATE states SET attributes='{"unit_of_measurement": "\u00b0F", "friendly_name": "Indoor Temperature"}' WHERE entity_id='sensor.indoor_temperature' AND attributes LIKE '%u00b0C%';

Once again, I recommend you have a backup copy available in case all these changes make a mess of the database.

1 Like
4

Oh, wow! Thanks for such a thorough (and prompt) reply!!

I had actually gotten part-way there myself before I asked the question. I shut down HA, copied the DB to my laptop, and opened it up with DB Browser. I even found the “states” table and was able to filter down to the entity of interest.

That’s where I chickened out. Not knowing anything about the structure of the DB, or if there are any relationships or dependencies I might be messing with, I figured I better stop there and just delete (well, rename, actually) the whole DB.

But with your instructions - you even wrote the SQL for me! - I should be good to go next time.

Than you!!!