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:
cp domoticz.db domoticz.db.bak
Start SQLite3 and log in to the Domoticz database with the command below:
Useful commands if you are going to make changes to the database.
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
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
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
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”.
SELECT * FROM Temperature_Calendar; shows all temperatures in the table “Temperature_Calendar”.
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
Already out. The Counter must of course also be raised properly.
Thanks again for your clear explanation here.
Thank you for your message and the compliments.
Good to read that you made it yourself!
This is great, and simply explained! I have used it successfully several times.
But now I have something strange. Maybe you understand what's going wrong.
I accidentally discarded a value, and now there is a hole in the database. Of
INSERT INTO Meter_Calendar (DeviceRowID,value,Counter,Date) VALUES (68,19312,1,'2022-04-02′);
tried to rewrite the value, and it works fine in the Month and Year view.
However, the cumulative yield (compairing generated), which should be a sum, goes from 149 to 333 (so it should be 168).
What's wrong here? And how can I fix this?
Thank you so much for your time.
Problem solved by using the right save command!
I copy the domoticz.db to sqlite, update the electricity values in table Multimeter_Calendar and copy the database back.
But, after a full restart of domoticz, the log of the P1 is still the same.
How can I update P1 history in domoticz and see the result in the p1-log-graph?
The historic values of the P1 meter are stored in table Multimeter_Calendar. But you have to be sure that you update the right IDX. In the database the column is named DeviceRowID.
I've updated my P1 values a few times in the past without any problems. Can you show an example of what you did?