Domoticz : How to remove fancy values ?

Background

Sometimes, when using a self managed counter, you are getting fancy values. Could be test, could be an error, could be wrong value read from a sensor .... Basically, it happens. This is a problem since it is faking your statistics. There is a way to remove those values but it does not always work. Let's find out how to solve this problem.

The problem

When looking at the history graph for the last 5 days, there is a spike here (in case of you wonder, this is a water meter graph) :

Domoticz offers a way to remove this value by "shift-clicking" it and then ask you confirmation to remove :

The thing is, it does not always works especially when the spike is composed of dozen of values, you can spent hours shift-clicking ... I have found a more radical way to sort this out !

The solution

Of course all those data are written in a database. The database is located on the domoticz folder in my case /home/pi/domoticz/domoticz.db

Take note of the sensor number, we will need it later :

Domoticz runs on a SQLite3 database and to perform any modifications, you need a client. Before, a little disclaimer : 

⚠️ EDITING THE DOMOTICZ DATABASE IS DANGEROUS ⚠️

First thing first, let's do a backup of the existing database, but before we have to shutdown domoticz to make sure everything is in clean state : 

$ sudo service domoticz.sh stop

$ cp domoticz.db domoticz.db.back

Now, we can play around with the DB !

Install sqlite3 client (if not installed already)

$ sudo apt-get install sqlite3

[...]
$ sqlite3 domoticz.db

SQLite version 3.34.1 2021-01-20 14:10:07

Enter ".help" for usage hints.

sqlite>

SQLite syntax can be confusing ... it is not following the same standard as Oracle or mariadb/mySQL. Let's dig into it.

First, let's list all tables and enable headers display :

sqlite> .tables

Applications          MobileDevices         SetpointTimers

BackupLog             MultiMeter            SharedDevices

Cameras               MultiMeter_Calendar   Temperature

CamerasActiveDevices  MySensors             Temperature_Calendar

CustomImages          MySensorsChilds       TimerPlans

DeviceStatus          MySensorsVars         Timers

DeviceToPlansMap      Notifications         ToonDevices

EnOceanNodes          Percentage            UV

EventMaster           Percentage_Calendar   UV_Calendar

EventRules            Plans                 UserSessions

Fan                   Preferences           UserVariables

Fan_Calendar          PushLink              Users

Floorplans            Rain                  WOLNodes

Hardware              Rain_Calendar         Wind

LightSubDevices       SceneDevices          Wind_Calendar

LightingLog           SceneLog              ZWaveNodes

Meter                 SceneTimers

Meter_Calendar        Scenes

sqlite> .headers on

We need to check for the content of the Meter table, this is where the utility sensors are located

sqlite> SELECT * FROM Meter;

DeviceRowID|Value|Usage|Date

5693|1788429|0|2023-06-14 22:20:00

6009|1631882|0|2023-06-14 22:20:00

5693|1788429|0|2023-06-14 22:25:00

6009|1631882|0|2023-06-14 22:25:00

5693|1788429|0|2023-06-14 22:30:00

6009|1631882|0|2023-06-14 22:30:00

5693|1788429|0|2023-06-14 22:35:00

6009|1631882|0|2023-06-14 22:35:00

5693|1788429|0|2023-06-14 22:40:00

6009|1631882|0|2023-06-14 22:40:00

5693|1788429|0|2023-06-14 22:45:01

6009|1631882|0|2023-06-14 22:45:01

5693|1788429|0|2023-06-14 22:50:00

6009|1631882|0|2023-06-14 22:50:00

5693|1788429|0|2023-06-14 22:55:00

[...]

Ok we have a lot of data, let's use the sensor number we have secured earlier by adding a WHERE clause and filter out sensor IDX 6009 :

sqlite> SELECT * FROM Meter WHERE DeviceRowID=6009;

DeviceRowID|Value|Usage|Date

6009|1631882|0|2023-06-14 22:20:00

6009|1631882|0|2023-06-14 22:25:00

6009|1631882|0|2023-06-14 22:30:00

6009|1631882|0|2023-06-14 22:35:00

6009|1631882|0|2023-06-14 22:40:00

6009|1631882|0|2023-06-14 22:45:01

6009|1631882|0|2023-06-14 22:50:00

6009|1631882|0|2023-06-14 22:55:00

6009|1631882|0|2023-06-14 23:00:01

6009|1631882|0|2023-06-14 23:05:00

6009|1631882|0|2023-06-14 23:10:00

6009|1631882|0|2023-06-14 23:15:00

6009|1631882|0|2023-06-14 23:20:00

6009|1631882|0|2023-06-14 23:25:00

[...]

Ok this is much better, let's try to locate the data we are looking at. From the deletion attempt above, it looks like the value we are looking for is "1632239" and the date is June the 16th ... Let's fine tune the query :

sqlite> SELECT * FROM Meter WHERE DeviceRowID=6009 and Value=1632239 and Date like "2023-06-16%";

DeviceRowID|Value|Usage|Date

6009|1632239|0|2023-06-16 00:00:00

6009|1632239|0|2023-06-16 00:00:01

6009|1632239|0|2023-06-16 00:05:00

6009|1632239|0|2023-06-16 00:10:01

[...]

There are actually 102 rows returned, which explain why a simple shift click is not doing the job !

Let's rerun the same SQL query with a delete statement this time : 

sqlite> DELETE FROM Meter WHERE DeviceRowID=6009 and Value=1632239 and Date like "2023-06-16%";

sqlite> SELECT * FROM Meter WHERE DeviceRowID=6009 and Value=1632239 and Date like "2023-06-16%";

sqlite>

Our fancy rows are gone. We can quit sqlite client and restart domoticz!

sqlite> .quit

$ sudo service domoticz.sh start

Back to the webpage and ... "voilà" ;-)

No more fancy peak value, all clean !

I hope this helps !