Migrate InfluxDB data to HA long term statistics

Configuration
1

Hi,

Has anyone attempted to migrate sensor data stored in an InfluxDB database to the HomeAssistant Long Term Statistics (LTS) database?

Way back in the day, before HA had an LTS database, I deployed the InfluxDB add-on and started collecting sensor data for long term storage there. At the time, the HA database could only handle a few weeks data in the recorder database, so it was pretty much the only game in town. I used Grafana to produce analytics for long term trends and so on.

These days, the HA LTS database is very capable and plug-ins like ApexCharts provide more than enough options for charting data. Meanwhile, InfluxDB remains obscure, its query language fiddly, database clients rare and I find Grafana a faff.

But before I can give InfluxDB the boot, I would really like to import the older historical data in my InfluxDB database to the HA LTS database - there is a good few years of useful data there.

Theoretically, it can’t be too hard. The HA LTS database schema is well documented - a script wizard could generate MySQL import statements based on a bunch of InfluxDB queries. If I had any time, I might have a go myself…

…so I’m hoping someone else might have attempted this?

2

The live HA Database needs to stay small-ish to be able to react quickly to events as they happen.
People are generally exporting to influx not trying to pull more data in. It may not end well. also LTS is an hourly compilation of the data so you might loose resolution, in case that matters.

3

I’m OK with losing temporal resolution. Of course the HA database needs to be performant, but isn’t that MySQL’s strong suit? I find it hard to believe that a few years’ of hourly sensor data would have any significant impact on performance, otherwise, what is MySQL good for?

I had hoped that Influx might have become a bit more mainstream by now. It’s a cool technology and a cool idea, but it still seems to be pretty fringe when it comes to clients / tools and so on.

Still curious to know whether anyone has attempted this.