Correcting values in Domoticz database

The underlying database in Domoticz is a SQLite3 database. Make sure that you have the sqlite3 package installed.

sudo apt-get install sqlite3

Stop Domoticz just to be sure

sudo service domoticz.sh stop

Make a backup copy of the Domoticz database.

cd ~/domoticz
cp domoticz.db domoticz.db.bak

Start SQLite3 on the Domoticz DB

sqlite3 domoticz.db

Some useful commands
.header on
.mode column
.tables
.dump

Temperature values for today are stored in the Temperature table. Historic values are stored in the Temperature_Calendar.

SELECT * FROM Temperature_Calendar;

Show all values for a certain date or earlier

SELECT * FROM Temperature_Calendar WHERE [Date] = '2017-03-13';
SELECT * FROM Temperature_Calendar WHERE [Date] < '2017-03-13';

Show all values for a specific device. You can find the right DeviceRowID in the Domoticz interface by opening the graphs and looking at the values in the parameters of the URL.

SELECT * FROM Temperature_Calendar WHERE [DeviceRowID] = 8;

You can delete old values as follows (useful to test your query first with a SELECT):

SELECT * FROM Temperature_Calendar WHERE [DeviceRowID] = 107 AND [Date] < '2017-02-19';
DELETE FROM Temperature_Calendar WHERE [DeviceRowID] = 107 AND [Date] < '2017-02-19';

You can correct certain values by using the UPDATE statement for a device on a certain date.

UPDATE Temperature_Calendar SET [Temp_Max] = 20.9 WHERE [DeviceRowID] = 8 AND [Date] = '2017-03-13';

I'm not sure why it happens, but the records for the Sunday on which the summer time starts, get stored under the Saterday, which results in two Temperature_Calendar records with the same date. This translates in the graphs to a vertical jump. This can be fixed in the database by listing the entries for that date and updating the date of the second record.

SELECT * FROM Temperature_Calendar WHERE [Date] = '2017-03-25'
 
You have to choose one of the values that identifies the second record, because you have to tell the database which one to update (and you can't say the second stored record, AFAIK). I chose the Temp_Min value.

UPDATE Temperature_Calendar SET [Date] = '2017-03-26' WHERE [Date] = '2017-03-25' AND [Temp_Min] = 21.1;
Make sure that you filter on both DeviceRowID and on Date or you will be changing the value for all devices or for all dates. Exit SQLite3 with .quit.

.quit

Start Domoticz to check your results.

sudo service domoticz.sh start