Import Domoticz history

Hi,

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?

Hope anyone can help!

Regards,

Erwin

Hello @erwin.sprengers

I am also looking for this solution, if you find something I am very interested.

There is a thread about exporting the database on the domoticz forum:
https://www.domoticz.com/forum/viewtopic.php?f=6&t=29766

Greeting
dzjr

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.

I’ll report back with what I find!

2 Likes

@Dogegg And did you manage? I also want to export P1 data from Domoticz to HA Energy dashboard.

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. :smiley: 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. :slight_smile:

I was also checking with queries to see if anything was possible. Hopefully you find the right way.

Well, I managed :smiley: .

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.

From version 0.2.0 Domoticz is supported.

In 2019 I fitted my roof with PV and it shows beautifully in the charts :heart:.

1 Like

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.)

thanks in advance!

Hi Johan,

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 :slight_smile:

Thanks!

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.

1 Like

Can’t you look at the days hourly profile in the energy dashboard?

That said, I can always start at daily.

I will make my CSV match what ever you need.

But will be standard comma separated, with a column header row. Then I can make the columns timestamp, Wh/kWh

I will make the CSV to your spec :slight_smile: bit I’m on the train, so will have to be some time over the weekend.

Cheers though!

HI Johan,

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 :slight_smile:

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?

Enjoy viewing the history! :relaxed: Indeed you need to run each query one at a time. With existing data you can remove and insert it or update existing values.

image

(If I ever learn python and made this an add-on, all these manual steps could be done with one button click…)

Goodwe invertor

The tool only reads a domoticz P1/dsmr device with multiple entities.

Well I tried and I think I managed :slight_smile:

I always used this plugin to read my invertor data into Domoticz : https://github.com/dylian94/domoticz-GoodWeSEMS

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)

Thanks, very happy with this! :slight_smile:

1 Like

Hi,

I’m migrating from domoticz to HA, and trying to import my domoticz data.
When updating the history, I got this one:

UNIQUE constraint failed: statistics.metadata_id, statistics.start

insert into statistics (created, start, state, sum, metadata_id) values
("2017-01-23 02:00:00.000000", "2017-01-23 02:00:00.000000", 1767.393, 0.000, 67),

I did in small batches, and found in some tables, double dates. Corrected them.

I imported everthing succesfully but…

The gas meter today is minus the zero. I added history and thats showing OK, above zero.

The history of the consumption and production is floating, also strange

Yes I already restarted :slight_smile:
What could this be?
Kaifa E0003 with dsmr 4 setting

Edit, and the history is not per hour but per day unfortunately. Is that correctable?