Domoticz runs on a SQLite3 database. To make changes to the database, sqlite3 must be installed.<\/p>\n\n\n\n
Before making changes to the database, it is wise to temporarily stop Domoticz and to make a backup of the database.<\/p>\n\n\n\n
To stop Domoticz, enter the following command in the terminal:<\/p>\n\n\n\n
To make a backup of the Domoticz database, enter the commands below:<\/p>\n\n\n\n Start SQLite3 and log in to the Domoticz database with the command below:<\/p>\n\n\n\n Useful commands if you are going to make changes to the database. Enables or disables the display of headers<\/p>\n\n\n\n The column labels that appear on the first two lines of output can be toggled on and off with the \u201c.header\u201d dot command. In the following example, the column labels are disabled.<\/p>\n\n\n 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.<\/p>\n\n\n The sqlite3 program can display the results of a query in eight different formats: \u201ccsv\u201d, \u201ccolumn\u201d, \u201chtml\u201d, \u201cinsert\u201d, \u201cline\u201d, \u201clist\u201d, \u201ctabs\u201d and \u201ctcl\u201d. With the command When the mode is set as Returns all tables in the database.<\/p>\n\n\n Dumps the database in an SQL text format. If the table name is specified, only tables that match the LIKE pattern TABLE are dumped.<\/p>\n\n\n Temperatures of the current day are stored in the \u201cTemperature\u201d table. The historical values are stored in the table \u201cTemperature_Calendar\u201d.<\/p>\n\n\n\n The statement The statement If you want to see all temperatures before May 5, 2021, use the statement 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. <\/p>\n\n\n\nsudo service domoticz.sh stop<\/code><\/p>\n\n\n\ncd ~\/domoticz
cp domoticz.db domoticz.db.bak<\/code><\/p>\n\n\n\nsqlite3 domoticz.db<\/code><\/p>\n\n\n\n.header on<\/a>
.fashion column<\/a>
.tables<\/a>
.dump<\/a><\/code><\/p>\n\n\n\n.Header on<\/code><\/h2>\n\n\n\n\nsqlite> .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\n<\/pre><\/div>\n\n\n
\nsqlite> .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\n<\/pre><\/div>\n\n\n
.fashion column<\/h2>\n\n\n\n
.fashion<\/code> a choice can be made as to how the output format is displayed.<\/p>\n\n\n\ncolumn<\/code>, each record is displayed on a separate line with the data aligned in columns, aiding readability.<\/p>\n\n\n\nsqlite > .mode column sqlite > select * from table1; select * from table1; 1 descrip1 2 descrip2\n<\/pre><\/div>\n\n\n
.tables<\/h2>\n\n\n\n
\nsqlite> .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\n<\/pre><\/div>\n\n\n
.dump<\/h2>\n\n\n\n
\nsqlite> .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;\n<\/pre><\/div>\n\n\nSELECT * FROM Temperature_Calendar;<\/code> shows all temperatures in the table \u201cTemperature_Calendar\u201d.<\/p>\n\n\n\nSELECT * FROM Temperature_Calendar WHERE [Date] = '2021-05-04';<\/code> let the temperatures of May 4, 2021.<\/p>\n\n\n\nSELECT * FROM Temperature_Calendar WHERE [Date] < '2021-05-04';<\/code><\/p>\n\n\n\n