Incorporating old sensor data when names change

[philmassie]

This integration is great, no question about that. Thanks again!!!

One thing that keeps catching me out though are name changes. I tend to run the updates and then a few days later realise that something changed and my old data has been left behind and new data has been logged to a new sensor id.

You can get in front of this if you change your entity names before the update I guess, but I find these difficult to get right, so I started poking round the DB with the idea of switching entity IDs.

I came across this which helped me a whole lot, https://github.com/janmolemans/hass_utils/blob/51403be6a1f180c02c6e66e1bdc75e493192006e/merge_sensor_statistics.ipynb

I ended up with the following: sensor_history_id_update.py

Use this at your own risk, and make sure to backup your DB before you mess with it.

The basic idea is to change the metadata ID associated with the old entity to that of the new entity, in the states, statistics_short_term and statistics tables. essentially transferring the old entities history to the new entity.

The error handling is a little rough around the edges, so use it carefully. I recently discovered Pyscript, which has a jupyter kernel which is how I interacted with the DB.

Hope this helps someone, it seems to work well...

[davidrapan]

Hi @philmassie, it's always nice to hear that! Thanks.

I know that the changing is proly pain in the ass, but it's needed for merging profiles with same registers and I'm almost done for example with removing profiles for devices that uses deye files and thus automate the selection, so I think it will be all worth it in the end.

I myself was investigating possible solutions for this history transfer/merge thing to be done in the integration cause I thought there must be a way... because renames happens in different integrations all the time but did not find any case where they would try to deal with it.

What did you find and write looks really good though! 🥳 I will link this discussion WITH A BIG WARNING in the wiki. 😉

Edit: And I think it could be turned into a service as well... with added support for MariaDB.

[philmassie]

Thanks very much, I hope it helps someone and maybe someone takes the time to improve it.
I'm not sure what you mean by service, but I'll google around and see what I can find. I agree, adding support for different DBs shouldn't be to hard with sqlalchemy.

I have no doubt the renaming will be worth it in the long run, good luck with that!