Add electricity values

[jannl]

Hi,

Started using Domoticz yesterday (on a Pi withRFXcom) and basically everything works fine so far.

I use a youless since 2 years, so I (Bidgely) collected a lot of data. Is it possible to add, at least the monthly totals, to Domoticz?

I searched the forum for this, but found nothing untill now.

Jan

[tommysharp]

Yeah loading historical data into domoticz from a previous system would be great!

[fisics]

Not sure if this is any help as I think you're trying to add historic monthly data.

But with my recent PiFace phototransistor counter setup. I found that the easy way to start an initial value reading was to temporary disable the PiFace hardware in settings and then using a JSON string to submit via a browser all you need to know is the IDX of the counter (found in the Devices page)...

Example, my IDX is 6 and reading was 2751.2 kWh - needed to add extra zeros to get decimal in the correct place:
http://raspi_ip:8080/json.htm?type=command&param=udevice&idx=6&nvalue=0&svalue=2751200

Then just re-enable the interface in Hardware settings. Very useful you can still submit data with the hardware interface disabled...

[jannl]

thanks, but I think this works when the device values itself are added.
I think I first will check the database with an sqlite browser

[jannl]

Just checked the sqlite database.
It looks like the table: Meter_Calendar is the one I need to update.
In this table there is a value (in Watt) for every day.

Anyone played with this?
Is it sufficient to add values for every day? (I can compute daily averages from the history data I have).

[sirus]

Hi Jan_nl, All,

First post for me and it seams to this forum is well interesting.

I have just installed a domoticz db on a raspberry and and wantn now to put water consuption for the past days and month.
It is quite similar that your requets and I wonder if you success to update your db as planned ,

I have try to fill meter_calendar but it does not appear in the domoticz interface.

Did you achieve to do it with electricity values ? Did someone here achieve it ?


Sorry I know that it is a old topic but the question has not been replied yet .


Thanks and br,
Emmanuel

[jannl]

I manually added some data for 2 months, not sure if I will do this for pas years. I made an XLS with some macro;s to set the data in such a way I can copy/paste an SQL statement in sqllite browser.

So copy the domoticz.db to windows, update and copy back. In the meantime Domoticz is off for some time

[sirus]

Hi, and thanks for this info.

this is what I have try.

I have first put daily data into meter_calendar but still not appear in the domoticz curve

Here my sql statment executed succesfully:

insert into meter_calendar values
(22, 12,0, "01/01/2015" ),
(22, 9,0, "02/01/2015" ),
(22, 22,0, "03/01/2015" ),
(22, 7,0, "04/01/2015" ),
(22, 8,0, "05/01/2015" ),
(22, 15,0, "06/01/2015" ),
(22, 12,0, "07/01/2015" ),
(22, 16,0, "08/01/2015" ),
(22, 21,0, "09/01/2015" ),
(22, 21,0, "10/01/2015" ),
(22, 13, 0, "11/01/2015" ),
(22, 24,0, "12/01/2015" ),
(22, 10,0, "13/01/2015" );

Data apeared in the db but not in domoticz curve.

Any Idea?

Maybe I need tyo put only monthly data into meter_calendar ?



Emmanuel

[sirus]

So after a lot of test I finaly get the good setup.
I will paste here is it could help :

sqlquery look like this :
INSERT INTO `Meter_Calendar` VALUES ('ID of the device','consuption of the day','counter value','day');
INSERT INTO `Meter_Calendar` VALUES ('22','12.7','471.27','2015-01-01');
INSERT INTO `Meter_Calendar` VALUES ('22','9.7','471.367','2015-01-02');
INSERT INTO `Meter_Calendar` VALUES ('22','22.1','471.587','2015-01-03');
INSERT INTO `Meter_Calendar` VALUES ('22','7.1','471.658','2015-01-04');
INSERT INTO `Meter_Calendar` VALUES ('22','8.3','471.741','2015-01-05');
INSERT INTO `Meter_Calendar` VALUES ('22','15.2','471.893','2015-01-06');
INSERT INTO `Meter_Calendar` VALUES ('22','12.1','472.014','2015-01-07');
INSERT INTO `Meter_Calendar` VALUES ('22','16.1','472.182','2015-01-08');
INSERT INTO `Meter_Calendar` VALUES ('22','21.3','472.395','2015-01-09');
INSERT INTO `Meter_Calendar` VALUES ('22','21.4','472.609','2015-01-10');
INSERT INTO `Meter_Calendar` VALUES ('22','13','472.739','2015-01-11');
INSERT INTO `Meter_Calendar` VALUES ('22','24.3','472.983','2015-01-12');
INSERT INTO `Meter_Calendar` VALUES ('22','10.4','473.087','2015-01-13');
INSERT INTO `Meter_Calendar` VALUES ('22','15.6','473.243','2015-01-14');

Hope it will help

Anyway thanks Jan_nl

Some pict from domoticz and sqlite

water.png (125.23 KiB) Viewed 9110 times

sqlite.jpg (91.77 KiB) Viewed 9110 times

[Deshke]

The first row was added in there because of youless and domoticz itself.
I added the other 3 rows, (did a manual metercheck in november 2014, december 2014 and januari 2015)

The number are not showing in the charts of domoticz. Maybe its because I don't know what to fill in field 'Value'.
Any suggestions?



ps: to help other people--->

-stop Domotics package service in Synology
- SSH into synology (wich is running Domoticz)
- copy domoticz database to a public folder of mine : scp /volume1/@appstore/domoticz/var/domoticz.db /volume1/homes/admin/domoticz.db
- download domoticz.db file to local computer (through GUI is the easiest)
-open domoticz.db with an SQLite databse browser (like SQLiteStudio) and add the records in Meter_Calendar
mine were :

INSERT INTO `Meter_Calendar` VALUES ('7','','2004023','2014-11-26');
INSERT INTO `Meter_Calendar` VALUES ('7','','2196662','2014-12-21');
INSERT INTO `Meter_Calendar` VALUES ('7','','2424571','2015-01-10');

-upload domoticz.db back up to public folder op synology (via GUI)
-and ssh copy domoticz.db from public folder back to domoticz app folder : scp /volume1/homes/admin/domoticz.db /volume1/@appstore/domoticz/var/domoticz.db
-start Domotics package again through package center on Synology

[sirus]

Hi,

I see 2 possibility to solve your issue here :
- both value and counter column have to be filled with the appropriate data. ValueN= CounterN - Counter N-1 (be carefull on the unit, compare with my value bellow)
- In my case I have one row / day. Try to fill all days between first and last day.

Let me knwo if it help.

Br,

[Deshke]

Oke ill add all the days of januari (to start with) with their values (ill calculate Some numbers te fill in) tomorrow.

Ill report back here. Thx for the advice !

edit: it worked but I removed it again BUT i worked (maybe helpfull for someone else who wants to add values from another smartmeter)

I only had the first and last metervalue so I had to add an average wich does not display a correct graph

[mistysnip]

Is there a way to increase the polling rate of the youless? I'm only getting hourly values.

Thanks

[rgvandenbosch]

Hi there,

Inserting data into the meter_calendar is not leading to what you (and I at the moment) want. Let me try to explain why.

In the settings you can setup the logging for X days. After X days the data from meter table is accumulated in the meter_calendar table.

If you like me are missing data from one week because of a crash, as soon as you restart domoticz it starts counting again in the meter table.
This means that it just starts where it ended a week ago. Now. Simply adding the missing data into the meter_calendar table isn't correct because the values in the meter table are not updated by inserting data into the meter table due to the fact that after X days the totals of meter are written to meter_calendar. So if the meter table has the value 200 and i inserting the value 250 into meter_calendar the meter table doesn't 'know' this and just keeps counting from 200.

It would be very nice to have a function in which you can add data(points) and add the missing data manually (or maybe even from sql scripts) which also recounts the values in the meter table so that everything is updated consistently.

Simply adding data into the meter_calendar table isn't enough!

Kind regards,

RG van den Bosch

[hanssie]

A week ago, I installed the Eastron SDM120-Modbus kWh meter to measure the solar energy produced by my solar panels and my Growatt inverter. Although it is possible to read the Growatt energy production directly, I was not satisfied with the stability of the connection with Domoticz. Currently, I'm facing the following issue:

I have transferred the history of my solar energy to the Domoticz database. However, I have mixed feelings about the result. I can see the following historical information on the screens for:

• Usage last week

• Usage last month

• Usage last year

Unfortunately, I don’t see the additional information reflected in the "Comparing Usage" graph. Can someone tell me what I did wrong?

By the way, I made the following modifications to the "Meter_Calendar" table:

I added my solar information only to the meter_calendar table.
The calculated counter values needs to be negative because the Eastron kWh meter starts at 0 kWh. So, I have to count back from this value, resulting in negative counter values.
I’m looking forward to receiving your support.

[waltervl]

The compare graph uses the same values from the Meter_Calendar table. But it needs both a running counter and the delta.

What data did you send to the table?

[hanssie]

Yes, both the delta's and counters are added to the table (otherwise you could not see any history informtion).

Here are some screendumps:
Database:

screendump database.png (38.73 KiB) Viewed 760 times

screendump of the graphs:

graphs.png (213.49 KiB) Viewed 760 times

As you can see no information is shown of the month februari.

[waltervl]

It could be the negative counter values are not supported by the compare graph....

[hanssie]

Yeh, a negative counter is not accepted. The problem is solved after I changed the negative values into positive values.

[BartSr]

maybe interesting to investigate the database for P1 smart meter type. iso negative values a dedicated column saves the return values.