P1 Gas meter shows total instead of day usage

[FrankPJ]

Since the upgrade to 2022.2 a few days ago the P1 gas meter shows the total gas count instead of the running day count. This is the case in the device list and in the header of the utility dashboard box. The “last 24 hour” graph has, after 2 days, the right value.
Is this a setting I can change, or can I get the day count back.

[waltervl]

As this is the first time a hear this I suppose you first try to delete your browser cache. If issue is still present please post a screenshot. My P1 meter still shows correct data, only seems to miss the m3 unit of measure on total.

Screenshot_20221206-225651-597.png (46.61 KiB) Viewed 519 times

[willemd]

Same as waltervl, no problem here except for the missing M3 (don't know if it was there before).

The last 24 hour graph values are calculated from the differences between one meter value to the previous one (in the meter table). This table contains in my case the total gas count, so this shows the running increases in the graph.

For problem analysis, you could start with inspection of the relevant database tables, both on the current domoticz.db database and the database before the upgrade (if you make automatic backups you can go to the relevant daily backup folder and then start sqlite3 on that database to inspect the situation at that moment).

In my case:
1) the meter table contains the total gas count in the value field with an new record every 5 minutes. The usage field is 0.
2) the meter_calendar table contains the total gas count field of 00:00:00 hours in the counter field and the total daily increase in the value field.
3) the devicestatus field contains the total gas count in the svalue field and should correspond to the last record of the meter table.

The value in the top right corner of the device icon is a calculated field, using the current total gas count (I guess from devicestatus, could also be from last record of meter table) minus the counter value from the last record in the meter_calendar table. If that icon shows the total gas count then there should be something wrong in the meter_calendar table (a 0 value as last record?)

Root cause remains to be found ....first check the above.

[FrankPJ]

thanks WillemD, that sheds some light.
Device status and Meter tables seem to adhere to the rules. The Meter_Calendar table shows however, for the last 3 days (since the upgrade), the meter counter value in the Counter field BUT the same value in the Value field. It hasn't done the math.
The last row is yesterdays number so this only affects my usage last week/month graphs. this is consistent to what I see there.

The number I see in the device icon is the running counter value. I guess that should compare to the first entry of the meter table to derive the day count, which it does not do.

By the way I do see the m3 unity in the icon.

curious what causes this behavior and how to get this back working.

[willemd]

Below is a small python program to correct the meter_calendar table. It will only correct the value field so it equals the difference between two consecutive counter fields. It will not affect the counters.

1) make a backup of your database
2) safe the code in a script, for example called "metercorrection.py"
3) adapt line 17 where it currently shows "<133". Replace it by "=idx" with idx being the number of your faulty device.
4) run the script by typing "python3 metercorrection.py" in the commandline

It will show where it corrects your data. You can have a test run first by commenting out the line starting with cur2.execute.

But the question remains, why is it storing the wrong value in the first place?
Any unusual records in the meter table for this device? Can you post the contents?

The domoticz daily switchover routine uses the following select and then calculates the changevalue by subtracting min from max.
SELECT MIN(Value), MAX(Value), AVG(Value) FROM Meter WHERE (DeviceRowID=idx AND Date>='yesterday' AND Date<='today 00:00:00')
Please replace idx by your device and yesterday and today by the date in format "yyyy-mm-dd"


Python code below

Code: Select all

import sqlite3
from sqlite3 import Error


def create_connection(db_file):
    conn = None
    try:
        conn = sqlite3.connect(db_file)
    except Error as e:
        print(e)

    return conn


def check_all_devices(conn):
    cur = conn.cursor()
    # the following select statement determines which devices to include.
    cur.execute("SELECT * FROM Meter_Calendar WHERE DeviceRowID <113 ORDER BY DeviceRowId, Date")
    rows = cur.fetchall()

    previousID=""
    for row in rows:
        print(row,end=" ")
        if row[0]!=previousID:
            # first row of a device
            counter=row[2]
            previousID=row[0]
            print("first row of device ")
        else:
            # determine difference this row and previous row
            counterincrease=row[2]-counter
            if counterincrease>=0:  # otherwise counter itself is wrong, so ignore
                if row[1]!=counterincrease:
                    print("Value ",row[1]," incorrect, should be : ",counterincrease, ", required correction : ",counterincrease-row[1])
                    # remove # from below two lines to actually execute the corrections
                    cur2=conn.cursor()
                    cur2.execute("UPDATE Meter_Calendar set value=? where DeviceRowId=? and Date=?",(counterincrease,row[0],row[3]))
                else:
                    print("Value correct")
            else:
                print("negative increase, something else is wrong: ignoring row")
            counter=row[2]


def main():
    # define connection, note prefix r means raw string
    database = r"/home/pi/domoticz/domoticz.db"

    # create a database connection
    conn = create_connection(database)

    # call function to select data and check integrity
    with conn:
        print("Processing records from Meter_Calendar ...")
        check_all_devices(conn)

if __name__ == '__main__':
    main()

[FrankPJ]

I did my repairs on the meter_calendar table. This corrected the presentations in the graphs.
As you suggested I interrogated the meter table. It contained a "0" halfway the table. By replacing it with a value between those of the rows above and below the icon started showing the right running day consumption.
My interpretation is that the day use is derived from the device status value minus the minimum value of the meter table, where you would expect the first row.

This meter table just has the data over 24 hours but the issue was over 3 days. Maybe it was a single data glitch that propagated over the tables during the days through the calculations and its solved now. When there is more structural thing going on I will see over the next days.

Thanks Willemd, this was very helpful.

[willemd]

I also don't understand why the logic to determine end-of-day-counter and day-changevalue is using min/max instead of first and last. This creates all kind of issues. See also:
https://github.com/domoticz/domoticz/issues/5437

[FrankPJ]

all running fine now for a few days; issue solved, thanks

[waltervl]

With the script? Then the issue is not solved....