just fired up my new Pi4 with HA.
For now all looks very easy, and integrations are set up fast.
I used to use a Pi3 for Domoticz with a P1 connection to my smart power and gas meter.
I would like to retain the history I had on my Domoticz device, and have it imported into HA.
Exporting form Domoticz seems easy, but I cannot find an option to import data into HA?
Also Google did not send me any useful suggestions.
Did anyone try to import historical data already, and if so, how?
Reviving this thread as I also want to do this!
Iāve managed to export Domoticz data before and find the energy usage tables in the database.
I guess the next step is to see if I can manually edit the HA database and add in the data from Domoticz.
For your information, as a Software Developer Iāve investigated a little bit because I want to achieve the same thing. Exporting loads of history from Domoticz and importing/viewing this history in home-assistant. In my case itās energy/gas sensor history and solar inverter history.
But I failed. TLDR: My conclusion is that home-assistant uses relative sensor information for its graphs and Domoticz has absolute sensor information. I was too lazy to calculate all relative values per month (best guess, please correct me).
Long story
My best guess was exporting dsmr history as csv or read the backup file with sqlite (select * from DeviceStatus). For importing sqlite-web could be used; very handy.
The actual problem is with transforming the data from different data schemes. I was hoping to do this with some manual replace-allās and then run the sql query to import history. However because home-assistent uses the sum field in statistics to show its graphs you need to ācalculateā this value.
This not only requires actual coding but if you already have sensor information, you need to correct it or clean up. My current guess is that the sum fields starts at 0 every month, so ideally you would want to time your migration to avoid this hassle.
By this time I figured I needed to consult the documentation to find out if it could be done easier haha. But looking at the code of home-assistent might prove more valueable. Maybe using statistics_short_term with a run to automatically let home-assistent do the calculations is more easy.
If I ever continue my quest. Iāll keep this thread updated.
I created a tool to import data into Home Assistant in Electron. You need to be a bit techy to get the options right and always create a backup before importing values.
Hey, i have raised an issue on your github repo. firstly, thanks for the work you have done.
What i would like to do is create a set of history entities, based on data from influxDB. so basically can put it into any CSV format thaty you could support. if you can put together a generic CSV importer, that would be perfect!
I can basically get any info required using a influx query. whether that is W, Wh, agregated at and time period (i was thining 15m for history.)
could you please update the instructions a bit for people who are less experienced with Electron? Iām running HA in a docker (not the full OS) and have installed a SQLite browser docker. But I donāt know how to get started with your solution. I would love to import my 4 year history, before I can say goodbye to Domoticz
Iāve updated the readme with āhow to startā and updated Palituās issue.
@palitu Home assistant only keeps a daily record for history, so a record for every 15 minutes might be a bit to much and give weird results in the chart.
thanks for the updates, Iām almost there but I get errors with the import script.
I made a backup of the HA DB and opened it in SQLiteBrowser. There I loaded one of the generated import scripts (Gas) but it throws this error. Any idea?
Execution finished with errors. Result: near "update": syntax error At line 1: update statistics set sum = sum + 7420.418 where metadata_id = 7 and start > "2022-07-08 02:00:00.000000"
Edit : figured it out, I tried to run the 3 queries at onceā¦ Managed to import the history, but had to delete existing data or it would give this error : (UNIQUE contraint failed: statistics.metadata_id, statistics.start)
Thatās fine, thatās just 2 weeks missing, but now I have 4 years of history back
Now to figure out the solar data. I have a Goodwe invertor with data in the domoticz db, is there a chance this is usable with the tool?
And indeed the data was found in the database. I lookup up the corresponding sensor ID in the HA database (94 in my case) and used this to create the import script.
I made a slight modification to the script where I changed āinsert into statisticsā into āinsert or ignore into statisticsā, otherwise I was not able to keep the existing data.
And now I see my history till the point I installed my PV (october 2020)
TLDR: My conclusion is that home-assistant uses relative sensor information for its graphs and Domoticz has absolute sensor information. I was too lazy to calculate all relative values per month (best guess, please correct me).
.
Indeed you need to run each query one at a time. With existing data you can remove and insert it or update existing values.
