Correcting values in database

[GammaKappa]

I use an incremental counter to log my electricity use.
Every 5 min the actual consumed KWh value in this period is sent to the counter via a MQTT message.
This was working fine for more than a year now.

Due to a power outage on 7 dec a wrong KWh value (more than 1000x than normal value) was sent to the counter.
This of course messed up all the graphs, hourly, daily, monthly and comparing usage graphs.

I deleted the erroneous day value (7 dec) using Shift delete on the graph in Domoticz.
The 'comparing usage' graph however still contained the extreme high value for December

In de database for Meter_Calendar I saw that the total 'Counter' values after 7 dec where still too high, so I manually corrected these to match the 'Value'
('Value' + previous day 'Counter' = new 'Counter').

After this correction all graphs are back to normal ... until the next day.
Then the new total 'Counter' is back to the old (too high) value.

All database corrections where done 'offline' i.e. using Backup - edit in SQLite - Restore

I am obviously doing something wrong.
How can I correct the Counter value properly and permanently ?

[willemd]

You will also have to update the current value of the counter to the correct value.
You can use a small dzvents script for that with the domoticz.devices(IDX).updateCounter(currentCounterValue) for that.
Obviously replace IDX and currentCounterVlaue by the correct numbers.
Otherwise the high value will still be in the current counter and you get an very high jump from your corrected database value to the new database value that is determined using the current counter.

And probably, after you have done the updateCounter, you will have to wait 5 minutes until this value is also visible in the meter table (the meter table contains the 5 minutes values) and then delete the previous values of today for that devicerowid, because the end-of-day switchover uses the difference between min and max values to determine the new value for the meter_calendar.

Needless to say : make a backup first. It is easy to make a mistake (for example forget the devicerowid in the delete statement )

[psubiaco]

I believe that you have to:
1. stop domoticz
2. connect to the database using the command sqlite3 domoticz.db
3. modify the counter values in Meter_Calendar (or other table):
.tables
select * from Meter where DeviceRowID=1722 order by Date;
update Meter set Value=Value-56000 where DeviceRowID=1722 and Date>='2023-11-28 21:10:00';
update Meter_Calendar set Value=Value-56000 where DeviceRowID=1722 and Date>='2023-11-28 21:10:00';
4. also, you have to modify the last value for that device in
update DeviceStatus set sValue='6;518227.4281' where ID=1722;

Of course you have to use the right idx value corresponding to your device, and right values and date.
The examples above are only examples: they worked for me when I got a bad value in the energy meter.
The step 4 is used to set the current value of the meter: your problem was that you updated the Meter and Meter_Calendar tables, but not the DeviceStatus tables, so when domoticz restarted, it continued to use the bad value.
I hope this can be helpful for you!

[GammaKappa]

As suggested I also corrected the 'DeviceStatus' sValue.
The 'comparing usage' graph is now back to normal.

The KWh value for today however showed one large negative peak (the correction).
So I also corrected that wrong value in Meter_Calendar.
Now everything looks OK again.

Thank you for your tips !