Replaced my zigbee bridge, but now I need to backfill sensor information post-migration

I recently swapped out my ConBee II for an SMLight SLZB-06. There’s no looking back for me, as sending the data over GigE to Zigbee2MQTT is a million times better than using a USB stick. However, switching from the Phoscon integration to Zigbee2MQTT has left me with a bunch of stranded data from the past year, particularly temp / humidity performance in various rooms of the house across different seasons and weather conditions that is informing my ongoing renovation work in this old house. Now I’ve got completely rock-solid stable sensor data coming in, but nothing from before Feb 2 when I swapped over the devices. To compound the problem, I stupidly deleted the old integration and all the devices/entities related to it once they were all working on the new on.

So I’m wondering if anyone else has had a similar migration and has found a good way to backfill the old data. I have Home Assistant backups with all the old data intact, so I can pull it out of there no problem. And these are all the same devices w/ same measurement precision and units, so should be transparent, I think.

I’m just wondering about the tidiest way of importing into the new Zigbee2MQTT devices. All advice welcome!

Have not done this before, only copied inside db and that worked. I am on Mariadb and I can import data, after that I would copy that data into the target tables, I guess this is statistics only…and whilst copying use the new metadata_id…so sql only

btw

What is so much better then? I am OK with my conbee II stick and the mesh.

So, off topic, but in terms of improvement: three key factors. (1) Location: It’s a big house with a lot of thick masonry walls, so quite important to have zigbee bridge in a central location, but my server room is in the cellar in the front. So I had to use a 15m active USB cable to get the conbee device central enough to pick up all my zigbee devices (and yes, I tried tradfri repeaters, and they just didn’t do the job). This new device uses poe and I can place it pretty much anywhere I need to as I have PoE switches in strategic places and ethnernet is a lot easier to run through conduit than active USB cables. (2) Mesh Reliability / Discovery: the conbee would continually lose/drop devices. I replaced it and continually updated firmware, but could never get a reliable zigbee mesh. Only about 80% of devices would stay in the mesh reliably. Discovery was also a royal pain for a lot o devices, which would take 5-10 minutes of trying. WIthout any other changes the SMLight device does discovery almost immediately for every device in the house (it is located right next to where the conbee was on it’s active USB cable, so not repositioning sorting the issue), and I’ve not lost a single device in two weeks. It has completely renovated my understanding of zigbee responsiveness and reliability which I had just assumed were challenges I had to accept and work around. (3) Simplified HW config: I run proxmox with HAOS in a virtual machine. Configuring USB passthrough isn’t massively challenging, but taking the data in via MQTT and ethernet is much simpler. And, I think, better for forward compatibility as my zigbee bridge isn’t so tightly tethered to the server.

Appreciate the details, thanks, I donot believe to have a ‘big’ house but the concrete walls are bugging which I resolved via cheap plugs. Is very stable (since almost 2 yrs). Only thing is outside but …more plugs :slight_smile:

Ok, so I’ve dumped a backup and opened up home-assistant_v2.db in a sqlite browser. For the curious, this is located within homeassistant/data in the backup. From what I can tell, it looks like I’ll need to generate a table tracking the correspondence of metadata_id in the statistics table (some details in statistics_meta, but my naming conventions weren’t disciplined so this isn’t all useful) so that I can dump statistics data for sensors I care about, then modify the id as it has changed in the new instance and then finally import this curated set of data into the same table in my current homeassistant database. Will report back on progress.