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 
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?
Does this method also work when using MariaDB add-on as database?
Just to be sure before I ruin everything 
Hi! I also imported all my history succesfull, but just like @Robert-N I have a negative value on 30-12.
In my case it is not the gas meter but the electricity meter.
Removing all values on 30-12 from the database will result in a negative value on the next day.
Anyone managed to fixed this?
I may have run into the same issue as you did.
On 2022-12-25 my Home Assistant was born
So, my Home Assistant database contains data from 2022-12-25 until now.
My Domoticz database (Domoticz is still running) has data from 2019-09-05 until now.
To solve the UNIQUE constraint failed: statistics.metadata_id, statistics.start error I imported data from 2022-12-24 and before.
What I missed was the date in the 2 SQL statements shown below
update statistics set sum = sum + 2696.769 where metadata_id = 1 and start > "2023-01-05 02:00:00.000000"
update statistics_short_term set sum = sum + 2696.769 where metadata_id = 1 and start > "2023-01-05 02:00:00.000000"
An update statics should be done only on the records created by HA. So in my case I needed to changed the date to 2022-12-24, because I should only update records from 2022-12-25 until now.
That fixed my problem. Below my results
Be carefull with the
If you end up, like I did, with executing this SQL statement twice, your sum value will also be doubled.
In my case I had to substract my sum values again 
Note the - 2696.769 in the SQL statements below.
update statistics set sum = sum - 2696.769 where metadata_id = 1 and start > "2022-12-25 02:00:00.000000"
update statistics_short_term set sum = sum - 2696.769 where metadata_id = 1 and start > "2022-12-25 02:00:00.000000"
Sorry, I feel really stupid, but I cannot get this tool to work for me.
I’m trying to import my domoticz power data.
The Domoticz database is read correctly, and I get the following output (59 is the daily kWh consumption in Home Assistant):
update statistics set sum = sum + 845.982 where metadata_id = 59 and start_ts > unixepoch("2023-03-28 02:00:00.000000")
update statistics_short_term set sum = sum + 845.982 where metadata_id = 59 and start_ts > unixepoch("2023-03-28 02:00:00.000000")
insert into statistics (created_ts, start_ts, state, sum, metadata_id) values
(unixepoch("2022-08-25 02:00:00.000000"), unixepoch("2022-08-25 02:00:00.000000"), 1360.979, 0.000, 59),
(unixepoch("2022-08-26 02:00:00.000000"), unixepoch("2022-08-26 02:00:00.000000"), 1361.223, 0.244, 59),
I get the error: Error importing file: list index out of range.
No matter how I change the settings, I keep getting this error. Also when I try to import a single data point.
delete from statistics where metadata_id = 59
delete from statistics_short_term where metadata_id = 59
insert into statistics (created, start, state, sum, metadata_id) values
("2022-08-25 02:00:00.000000", "2022-08-25 02:00:00.000000", 1360979.000, 0.000, 59)
Also does not work.
I’ll double check all the HA db fields and see if something has changed.
I can run the sql script without errors. What version of home assistant are you running?
Many thanks! I’m running Home Assistant 2023.3.5 in a Docker on my raspi4. Maybe it’s a problem with the kWh vs Wh. I’ll play with is a bit, but good to know that the tool should still work.
If you run this sql query, you do get a result? Maybe 59 is the wrong sensor?
SELECT *
FROM statistics_meta
where id = 59
You can use the multiplier to change to different units.
Thanks @johanbos for your marvelous tool. I managed to import all of my Domoticz history into my HA MariaDB.
The following changes need to be made when you use MariaDB on HA.
This worked like a charm for me. I cannot tell you how happy I am that I have all my history in HA now.
Thanks again!
Hi @johanbos,
Thank you for creating this awesome tool! I imported all my Domoticz history in to Home Assistant. There are 2 things I’m not sure about though, maybe you can shed some light on them:
-I imported until 2023-05-29. On 2023-05-30 there is a hugh spike in generated power. Is this the expected behaviour?
-if this is related to the ‘update sum’ setting (which I set to update) what are the consequences of setting it to not alter the sum?
Hi,
complete newbie here.
I just managed to setup DSMR Reader and integrate it in HA. I’d love to import the Domoticz data as well, but when running the sql script to find the ID, none of the results are in the sensor.dsmr category.
What am I missing here?
Thanks!
Thank you johanbos, your software works nickel.
I am in the process of migrating from domoticz to home assistant.
You wouldn’t be by chance how to migrate the temperature probes in the same way, it will be great 
Hi @johanbos,
I have downloaded the application (release v0.2.1-ci.140), but as soon as I import the Domoticz *.db file, the applications closes/crashes. Is there an known issue with this version?
Same issue here
Same here - I’ve tried on both Windows and Linux.
The interface silently exits immediately after I select the domoticz database backup.
Same issue here (Windows)
update: I downloaded Home.Assistant.Import-win32-x64-0.2.1-ci.17 and that worked fine. Thanks!