Manually change data in database

[bnwgraaf]

I have an electric car which I can charge at home. To measure the used kWh, I have a kWh meter and an esp8266 wifi board which counts pulses from the kWh meter. The pulses are then sent to Domoticz, which is running on a raspberry pi.
This works great!

But while using this setup, I have made several changes to the code on the esp8266. It takes moments of high and low tarivs into account to split in a P1 meter in Domoticz in the T1 and T2 values.

Because of some wrong parts of code, the values in the totals under the P1 meter >> log >> rapport are not right, so I am wondering if I can manually change this in the sql database.

When opening the database I can find the values under "multimeter_calendar.

Is it safe to change those values manually?
Can I find somewhere what values are in those columns?
It has:
Value 1, 2, 3, 4, 5 and 6 and
Counter 1, 2, 3 and 4

Are there some tutorials about these database or more info about it?

[jvdz]

I have made changes before to a P1 device's data in the database with a sqlite command or sqlite express and there is no issue doing that as long as you know what you are doing. just stop domoticz, make an backup and update the data.
I figured out that the fields contain by looking at the sourcecode:

Code: Select all

// In DeviceStatus table, index 0 = Usage 1,  1 = Usage 2, 2 = Delivery 1,  3 = Delivery 2, 4 = Usage current, 5 = Delivery current
// In MultiMeter table, index 0 = Usage 1, 1 = Delivery 1, 2 = Usage current, 3 = Delivery current, 4 = Usage 2, 5 = Delivery 2
// In MultiMeter_Calendar table, same as Multimeter table + counter1, counter2, counter3 and counter4 when shortlog is False

so:

Code: Select all

Value1 = Usage1
Value2 = Return1
Value3 = Current Usage
Value4 = Current Return
Value5 = Usage2
Value6 = Return2
Counter1 = Counter for Usage1
Counter2 = Counter for Return1
Counter3 = Counter for Usage2
Counter4 = Counter for Return2

Here is an example php source that will list the infomation of a P1 device IDX as of a specified date:

Code: Select all

<?php
$db = new SQLite3('domoticz.db');
$startdate = "2022-04-01";

ShowIDXInformation("375");

//Show P1 info
function ShowIDXInformation($IDX)
{
    global $db,$startdate;
    echo "=====================================================\n";
    echo "===  Start Listing for $IDX starting from ".$startdate." \n";
    echo "-----------------------------------=-----------------\n";
    $sql = "Select rowid, DeviceRowID, Date, Counter1,Counter2,Counter3,Counter4, Value1, Value2, Value3, Value4, Value5, Value6
    from MultiMeter_Calendar
    where DeviceRowID = $IDX and Date > date('$startdate', '-1 day');"; // Last day is missing the COUNTER fields so skipping that
    $results = $db->query($sql);
    $results->reset();
    while ($row = $results->fetchArray()) {
            echo "--------------------------------------------------------------------\n";
            echo $row["DeviceRowID"]." ".$row["Date"]."\n";
            echo "\tUsage1=".$row["Value1"]."\tUsage2=".$row["Value5"]."\ttotal=".($row["Value1"]+$row["Value5"]) . "\tUsage1Counter=". $row["Counter1"]. "\tUsage2Counter=". $row["Counter3"]."\n";
            echo "\tReturn1=".$row["Value2"]."\tReturn2=".$row["Value6"]."\ttotal=".($row["Value2"]+$row["Value6"]) . "\tReturn1Counter=". $row["Counter2"]. "\tReturn2Counter=". $row["Counter4"]."\n";
    }
    echo "---------------------------------------\n";
    echo "=== Done $IDX\n";
    echo "=======================================\n\n";
}

Jos

[bnwgraaf]

I have made changes before to a P1 device's data in the database with a sqlite command or sqlite express and there is no issue doing that as long as you know what you are doing. just stop domoticz, make an backup and update the data.
I figured out that the fields contain by looking at the sourcecode:

Code: Select all

// In DeviceStatus table, index 0 = Usage 1,  1 = Usage 2, 2 = Delivery 1,  3 = Delivery 2, 4 = Usage current, 5 = Delivery current
// In MultiMeter table, index 0 = Usage 1, 1 = Delivery 1, 2 = Usage current, 3 = Delivery current, 4 = Usage 2, 5 = Delivery 2
// In MultiMeter_Calendar table, same as Multimeter table + counter1, counter2, counter3 and counter4 when shortlog is False

so:

Code: Select all

Value1 = Usage1
Value2 = Return1
Value3 = Current Usage
Value4 = Current Return
Value5 = Usage2
Value6 = Return2
Counter1 = Counter for Usage1
Counter2 = Counter for Return1
Counter3 = Counter for Usage2
Counter4 = Counter for Return2

Here is an example php source that will list the infomation of a P1 device IDX as of a specified date:

Code: Select all

<?php
$db = new SQLite3('domoticz.db');
$startdate = "2022-04-01";

ShowIDXInformation("375");

//Show P1 info
function ShowIDXInformation($IDX)
{
    global $db,$startdate;
    echo "=====================================================\n";
    echo "===  Start Listing for $IDX starting from ".$startdate." \n";
    echo "-----------------------------------=-----------------\n";
    $sql = "Select rowid, DeviceRowID, Date, Counter1,Counter2,Counter3,Counter4, Value1, Value2, Value3, Value4, Value5, Value6
    from MultiMeter_Calendar
    where DeviceRowID = $IDX and Date > date('$startdate', '-1 day');"; // Last day is missing the COUNTER fields so skipping that
    $results = $db->query($sql);
    $results->reset();
    while ($row = $results->fetchArray()) {
            echo "--------------------------------------------------------------------\n";
            echo $row["DeviceRowID"]." ".$row["Date"]."\n";
            echo "\tUsage1=".$row["Value1"]."\tUsage2=".$row["Value5"]."\ttotal=".($row["Value1"]+$row["Value5"]) . "\tUsage1Counter=". $row["Counter1"]. "\tUsage2Counter=". $row["Counter3"]."\n";
            echo "\tReturn1=".$row["Value2"]."\tReturn2=".$row["Value6"]."\ttotal=".($row["Value2"]+$row["Value6"]) . "\tReturn1Counter=". $row["Counter2"]. "\tReturn2Counter=". $row["Counter4"]."\n";
    }
    echo "---------------------------------------\n";
    echo "=== Done $IDX\n";
    echo "=======================================\n\n";
}

Jos

Thanks for your quick reply! This is exactly the info I needed.
I will try to make the changes.

[HvdW]

The php script works flawless. Thanks @jvdz Jos.

More than enough questions.
To start with. I dumped Gas data

Code: Select all

<?php
// copied from https://domoticz.com/forum/viewtopic.php?p=297402#p297402
// Data extracted from Meter_Calendar table

$db = new SQLite3('20230108_domoticz.db');
$startdate = "2019-11-01";

ShowIDXInformation("16");  //IDX 15 = P1 electricity, IDX 16 = P1 gas

//Show P1 info
function ShowIDXInformation($IDX)
{
    global $db,$startdate;
    echo "=====================================================\n";
    echo "===  Start Listing for $IDX starting from ".$startdate." \n";
    echo "-----------------------------------=-----------------\n";
    $sql = "Select rowid, DeviceRowID, Date, Counter, Value
    from Meter_Calendar
    where DeviceRowID = $IDX and Date > date('$startdate', '-1 day');"; // Last day is missing the COUNTER fields so skipping that
    $results = $db->query($sql);
    $results->reset();
    while ($row = $results->fetchArray()) {
            echo "--------------------------------------------------------------------\n";
            echo $row["DeviceRowID"]." ".$row["Date"]."\n";
            echo "\tUsage1=".$row["Value"]."\n";
    }
    echo "---------------------------------------\n";
    echo "=== Done $IDX\n";
    echo "=======================================\n\n";
}

Second.
I would like to add december 2019 and januari 2021 Gas data.
My RPI had some trouble and despite that I have all data collected in MinderGas.nl
I'd like to add those values to Domoticz.db with a simple php script like the one you showed us.

I thought of making a domoticz.db copy, export the data to a CSV file, delete these exported data from domoticz.db, add the 2019 and 2020 data and insert the lot to domoticz.db, finalising it with replacing the current domoticz.db with my my new created copy.

Is that possible?


Hein

[willemd]

The php script works flawless. Thanks @jvdz Jos.

More than enough questions.
To start with. I dumped Gas data

Code: Select all

<?php
// copied from https://domoticz.com/forum/viewtopic.php?p=297402#p297402
// Data extracted from Meter_Calendar table

$db = new SQLite3('20230108_domoticz.db');
$startdate = "2019-11-01";

ShowIDXInformation("16");  //IDX 15 = P1 electricity, IDX 16 = P1 gas

//Show P1 info
function ShowIDXInformation($IDX)
{
    global $db,$startdate;
    echo "=====================================================\n";
    echo "===  Start Listing for $IDX starting from ".$startdate." \n";
    echo "-----------------------------------=-----------------\n";
    $sql = "Select rowid, DeviceRowID, Date, Counter, Value
    from Meter_Calendar
    where DeviceRowID = $IDX and Date > date('$startdate', '-1 day');"; // Last day is missing the COUNTER fields so skipping that
    $results = $db->query($sql);
    $results->reset();
    while ($row = $results->fetchArray()) {
            echo "--------------------------------------------------------------------\n";
            echo $row["DeviceRowID"]." ".$row["Date"]."\n";
            echo "\tUsage1=".$row["Value"]."\n";
    }
    echo "---------------------------------------\n";
    echo "=== Done $IDX\n";
    echo "=======================================\n\n";
}

Second.
I would like to add december 2019 and januari 2021 Gas data.
My RPI had some trouble and despite that I have all data collected in MinderGas.nl
I'd like to add those values to Domoticz.db with a simple php script like the one you showed us.

I thought of making a domoticz.db copy, export the data to a CSV file, delete these exported data from domoticz.db, add the 2019 and 2020 data and insert the lot to domoticz.db, finalising it with replacing the current domoticz.db with my my new created copy.

Is that possible?


Hein

You can even load a CSV file with data into a table without removing the existing values first, as long as you don't load data for already existing dates and not for today. Of course, always wise to make a backup. Google for "load sqlite3 csv" for instructions.