Import Domoticz history

Third party integrations
22

I may have run into the same issue as you did.

On 2022-12-25 my Home Assistant was born :slight_smile:
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

image
image1311×1161 46 KB

23

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 :frowning: 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"
24

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.

25

I’ll double check all the HA db fields and see if something has changed.

26

I can run the sql script without errors. What version of home assistant are you running?

27

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.

28

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

29

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.

  1. The first select query you use to get the list of items in HA needs to be modified.
  • The database “statistics” needs to be written without the double quotes.
  • The item start being named start in the first line needs to be named anything else then start. I used start1.
  1. The queries created by the tool needs to be modified.
  • The command unixepoch doesn’t exist in MariaDB
  • Use UNIX_TIMESTAMP instead.

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!

30

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?

31

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!

32

Thank you johanbos, your software works nickel.:+1:
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 :ok_hand:

33

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?

34

Same issue here

35

Same here - I’ve tried on both Windows and Linux.

The interface silently exits immediately after I select the domoticz database backup.

36

Same issue here (Windows)

update: I downloaded Home.Assistant.Import-win32-x64-0.2.1-ci.17 and that worked fine. Thanks!

37

Ik keep getting “400: Bad Request” no matter what I do.
Query generated looks like:

update statistics set sum = sum + 15079349.000 where metadata_id = 328 and start_ts > unixepoch(“2023-10-25 02:00:00.000000”)

update statistics_short_term set sum = sum + 15079349.000 where metadata_id = 328 and start_ts > unixepoch(“2023-10-25 02:00:00.000000”)

insert into statistics (created_ts, start_ts, state, sum, metadata_id) values
(unixepoch(“2016-10-04 02:00:00.000000”), unixepoch(“2016-10-04 02:00:00.000000”), 2285001.000, 0.000, 328),
(unixepoch(“2016-10-05 02:00:00.000000”), unixepoch(“2016-10-05 02:00:00.000000”), 2288855.000, 3854.000, 328)

Anyone an idea?
I use SQLite Web

38

I just changed from Domoticz to HA and trying to import the data from the Domoticz database.
If i use the tool i should first do a query in HA SQL lite to get the Id (SELECT sm.Id, sm.statistic_id, etc…). But if i use this query in statistics i get the error Bad request.

When i use the queries with the correct id, the updates queries works fine, but the insert query returns an error.

What am i doing wrong?

Edit: When i delete the cr (hard returns) in the query the values are added in statistics but i don’t see the history yet.

39

artstam,

I got it working also.
The update in the history will show after clearing cache or reloading page.

40

How nice to see all the feedback and success stories. I try to keep the tool updated and based on the feedback I’ve added some items in Issues · Johanbos/home-assistant-import · GitHub.

41

How did you guys solve the ‘bad request’ issue?
I’m having the same with importing domoticz history into HA via SQL Lite Web.

Tried to remove the “cr (hard returns)” but that results in a 404 error.

Trying to import Gas, Power & Water.

update statistics set sum = sum + 2090.225 where metadata_id = 59 and start_ts > unixepoch("2023-12-27 02:00:00.000000")

update statistics_short_term set sum = sum + 2090.225 where metadata_id = 59 and start_ts > unixepoch("2023-12-27 02:00:00.000000")

insert into statistics (created_ts, start_ts, state, sum, metadata_id) values
(unixepoch("2016-12-02 02:00:00.000000"), unixepoch("2016-12-02 02:00:00.000000"), 2930.762, 0.000, 59),