How to fix data in the database
Sometimes sensor or counter data registered by Domoticz in the database may be wrong due to bad measure or counters inited from a wrong value.
What to do?
Check the device chart with all values, and use shift+click on the bad values to cancel them from the database.
In case of counters, if you have to change the current energy value (or else), you have to:
1. Stop domoticz (service domoticz stop or other command)
2. Make a backup of domoticz.db database: (cp domoticz.db backups/domoticz_20231216.db or something else)
3. Connect to the database using the command sqlite3 domoticz.db
4. Modify the counter values in Meter_Calendar (or other table) using the following queries:
.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';
5. also, you have to modify the last value for that device with the sql query
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.
The step 5 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!
How to export database backups in a external USB hard disk
If you're running Domoticz on a single board computer with SD or SSD memory, and you have an external hard disk connected by USB, to prevent the flash memory (that can be written only about 100 thousands times) I suggest to move the DOMOTICZ/backups directory on the external hard disk (that must be mounted at boot time, so it should be placed in /etc/fstab).
The following commands permit to enter DOMOTICZ directory, create a domoticz dir in the external hard disk, move the domoticz backup on the hard disk and create a symbolic link to it:
cd DOMOTICZ
mkdir /mnt/USB/domoticz
mv backups /mnt/USB/domoticz/
ln -s /mnt/USB/domoticz/backups .
How to disable database hourly backup
I also suggest to disable the hourly database backup, because it takes several seconds where automations stop working. To do this it's sufficient to remove the hourly directory and create a file with the same name, so Domoticz will not be able to create hourly directory and will skip the hourly backup:
cd /mnt/USB/domoticz/backups
rm -rf hourly
> hourly