import old non domoticz data

[nspierbundel]

Hi all, a short introduction,
I have a smart meter since 2013 and log the data every 5 minutes using a simple script (python and php) to a mysql database. After recently seeing domoticz the need to create a nice dashboard for my data is obsolete, Domoticz can do everything I want.
So now I want to convert my historical data to Domoticz.

I have historical data from my smart meter (p1 usb) in a mysql database, tables are as follow:
ID
date
1.8.1
1.8.2
2.8.1
2.8.2
1.7.0
2.7.0
24.3.0

can you explain the database structure in domoticz so I can create a script to convert the data.

Thanks
Daniel

[pj-r]

Not sure if I can but there is something:

You have tables for hourly values and daily values. The hourly and contains data for X days you have defined in settings.

Code: Select all

sqlite> .schema 'Meter'
CREATE TABLE [Meter] ([DeviceRowID] BIGINT NOT NULL, [Value] BIGINT NOT NULL, [Usage] INTEGER DEFAULT 0, [Date] DATETIME DEFAULT (datetime('now','localtime')));

Code: Select all

sqlite> select * from Meter WHERE DeviceRowID = 327 ORDER BY Date DESC LIMIT 10;
327|1077088|6760|2017-05-08 15:45:00
327|1077038|5950|2017-05-08 15:40:00
327|1076988|5920|2017-05-08 15:35:00
327|1076939|5870|2017-05-08 15:30:00
327|1076889|6670|2017-05-08 15:25:00
327|1076832|5600|2017-05-08 15:20:00
327|1076794|5250|2017-05-08 15:15:00
327|1076733|8550|2017-05-08 15:10:00
327|1076662|8390|2017-05-08 15:05:00
327|1076592|8410|2017-05-08 15:00:00

From last row the number is blinks so far: 1076592(for me 1000/kwh) and 8410 is 841W of power at that moment.

And for the daily values:

Code: Select all

sqlite> .schema 'Meter_Calendar'
CREATE TABLE [Meter_Calendar] ([DeviceRowID] BIGINT NOT NULL, [Value] BIGINT NOT NULL, [Counter] BIGINT DEFAULT 0, [Date] DATETIME DEFAULT (datetime('now','localtime')));

Code: Select all

sqlite> select * from Meter_Calendar WHERE DeviceRowID = 327 ORDER BY Date DESC LIMIT 10;
327|18437|1065712|2017-05-07
327|910|1047271|2017-05-06
327|2686|1046357|2017-05-05
327|11034|1043624|2017-05-04
327|17761|1032531|2017-05-03
327|8639|1014680|2017-05-02
327|16367|1005981|2017-05-01
327|13449|989532|2017-04-30
327|12262|976080|2017-04-29
327|19552|963734|2017-04-28

First value(KWH for that day) 18437 -> 18.437, second value pulsecount?

[nspierbundel]

In my current database I do not have blinks,

Code: Select all

mysql> select * from data limit 10;
+----+---------------------+-------+-------+-------+-------+-------+-------+---------+
| ID | date                | 1.8.1 | 1.8.2 | 2.8.1 | 2.8.2 | 1.7.0 | 2.7.0 | 24.3.0  |
+----+---------------------+-------+-------+-------+-------+-------+-------+---------+
|  1 | 2013-11-05 11:45:27 |  1062 |   661 |   699 |  1510 |     0 |     0 |  992000 |
|  2 | 2014-05-11 12:01:07 |  1821 |  1358 |   911 |  2018 |    60 |     0 | 1432934 |
|  3 | 2014-05-11 11:56:19 |  1821 |  1358 |   911 |  2018 |   230 |     0 | 1432934 |
|  4 | 2014-05-11 12:05:10 |  1821 |  1358 |   911 |  2018 |     0 |    30 | 1433632 |
|  5 | 2014-05-11 12:10:10 |  1821 |  1358 |   911 |  2018 |    10 |     0 | 1433632 |
|  6 | 2014-05-11 12:15:10 |  1821 |  1358 |   911 |  2018 |     0 |   170 | 1433632 |
|  7 | 2014-05-11 12:20:10 |  1821 |  1358 |   911 |  2018 |   230 |     0 | 1433632 |
|  8 | 2014-05-11 12:25:10 |  1821 |  1358 |   911 |  2018 |   140 |     0 | 1433632 |
|  9 | 2014-05-11 12:30:10 |  1821 |  1358 |   911 |  2018 |    90 |     0 | 1433632 |
| 10 | 2014-05-11 12:35:10 |  1821 |  1358 |   911 |  2018 |   220 |     0 | 1433632 |
+----+---------------------+-------+-------+-------+-------+-------+-------+---------+

explanation

Code: Select all

1.8.1 is usage 1
1.8.2 is usage 2
2.8.1 is return 1
2.8.2 is return 2
1.7.0 is current usage 
2.7.0 is current return
24.3.0 is gas

Daniel