Adjust values in Domoticz database

Domoticz runs on a SQLite3 database. To make changes to the database, sqlite3 must be installed.

Before making changes to the database, it is wise to temporarily stop Domoticz and to make a backup of the database.

To stop Domoticz, enter the following command in the terminal:

sudo service domoticz.sh stop

To make a backup of the Domoticz database, enter the commands below:

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

Start SQLite3 and log in to the Domoticz database with the command below:

sqlite3 domoticz.db

Useful commands if you are going to make changes to the database.
.header on
.fashion column
.tables
.dump

.Header on

Enables or disables the display of headers

The column labels that appear on the first two lines of output can be toggled on and off with the “.header” dot command. In the following example, the column labels are disabled.

sqlite> .headers off sqlite> SELECT * FROM doctors; 210 dr. John Linga MD 211 Dr. Peter Hall MBBS 212 Dr. Ke Gee MD 213 Dr. Pat Fay MD

Here in the example above, there is no header displayed for the doctor table. If we want to see the header of the columns of the table, the following command must be executed.

sqlite> .headers on sqlite> SELECT * FROM doctors; doctor_id doctor_name degree ---------- -------------- ---------- 210 Dr. John Linga MD 211 Dr. Peter Hall MBBS 212 Dr. Ke Gee MD 213 Dr. Pat Fay MD

.fashion column

The sqlite3 program can display the results of a query in eight different formats: “csv”, “column”, “html”, “insert”, “line”, “list”, “tabs” and “tcl”. With the command .fashion a choice can be made as to how the output format is displayed.

When the mode is set as column, each record is displayed on a separate line with the data aligned in columns, aiding readability.

sqlite > .mode column sqlite > select * from table1; select * from table1; 1 descrip1 2 descrip2

.tables

Returns all tables in the database.

sqlite> .tables backup log Mobile Devices scenes Cameras MultiMeter Setpoint Timers Camera Active Devices MultiMeter_Calendar Shared Devices Custom Images MySensors Temperature Device Status MySensorsChilds Temperature_Calendar DeviceToPlansMap MySensorsVars Timer Plans EnoceanSensors Notifications Timers Event Master Percentage Display Devices Event Rules Percentage_Calendar UV Fan Plans UV_Calendar Fan_Calendar Preferences User Sessions Floorplans Quality Link UserVariables Hardware Rain Users LightSubDevices Rain_Calendar WOLNodes LightingLog Scene Devices Wind Meter SceneLog Wind_Calendar Meter_Calendar SceneTimers ZWaveNodes

.dump

Dumps the database in an SQL text format. If the table name is specified, only tables that match the LIKE pattern TABLE are dumped.

sqlite> .dump Fan PRAGMA foreign_keys=OFF; BEGIN TRANSACTION; CREATE TABLE [Fan] ([DeviceRowID] BIGINT(10) NOT NULL, [Speed] INTEGER NOT NULL, [Date] DATETIME DEFAULT (datetime('now','localtime'))); CREATE INDEX f_id_idx on Fan(DeviceRowID); CREATE INDEX f_id_date_idx on Fan(DeviceRowID, Date); COMMIT;

Temperatures of the current day are stored in the “Temperature” table. The historical values are stored in the table “Temperature_Calendar”.

The statement SELECT * FROM Temperature_Calendar; shows all temperatures in the table “Temperature_Calendar”.

The statement SELECT * FROM Temperature_Calendar WHERE [Date] = '2021-05-04'; let the temperatures of May 4, 2021.

If you want to see all temperatures before May 5, 2021, use the statement SELECT * FROM Temperature_Calendar WHERE [Date] < '2021-05-04';

To return the values of a specific device we need the correct DeviceRowID. These can be found in the Domoticz interface by opening the graphs. In the address bar of your browser you will see the ID (idx) of the device. With the statement below you can display the temperature values of the relevant device.

SELECT * FROM Temperature_Calendar WHERE [DeviceRowID] = 8;

Old values can be removed from the database with the following statement:

DELETE FROM Temperature_Calendar WHERE [DeviceRowID] = 8 AND [Date] < '2021-05-04';

With the UPDATE statement you can change certain values. Suppose we want to adjust the maximum temperature of May 4, 2021 of the device with idx 8 from 20.9 to 12.3. For this we use the statement:

UPDATE Temperature_Calendar SET [Temp_Max] = 12.3 WHERE [DeviceRowID] = 8 AND [Date] = '2021-05-04';

If you want to adjust the maximum, minimum and average temperature of May 4, 2021 with one statement, use the following statement:

UPDATE Temperature_Calendar SET [Temp_Max] = 12.3, [Temp_min] = 5.3, [Temp_avg] = 7.5 WHERE [DeviceRowID] = 8 AND [Date] = '2021-05-04';

To exit SQLite3 use the command .quit after which you will return to the terminal.

Restart Domoticz to see the result of the changes with the following command:

sudo service domoticz.sh start