how to modify the short log sensors more than 7 Topic is solved
Moderators: leecollings, remb0
-
- Posts: 3
- Joined: Monday 04 September 2017 9:23
- Target OS: Raspberry Pi / ODroid
- Domoticz version: 3.8373
- Contact:
how to modify the short log sensors more than 7
Hello
I want to have in the data base more than 7 days form short log sensor .
How I do this?
I'm on pi 2 jessie lite and domoticz 3.8153.
I want to have in the data base more than 7 days form short log sensor .
How I do this?
I'm on pi 2 jessie lite and domoticz 3.8153.
-
- Posts: 3
- Joined: Monday 04 September 2017 9:23
- Target OS: Raspberry Pi / ODroid
- Domoticz version: 3.8373
- Contact:
Re: how to modify the short log sensors more than 7
thank you for your response
-
- Posts: 14
- Joined: Thursday 23 July 2020 11:47
- Target OS: Raspberry Pi / ODroid
- Domoticz version:
- Contact:
Re: how to modify the short log sensors more than 7
Still can't?
- waaren
- Posts: 6028
- Joined: Tuesday 03 January 2017 14:18
- Target OS: Linux
- Domoticz version: Beta
- Location: Netherlands
- Contact:
Re: how to modify the short log sensors more than 7
The reason why the max days for short log sensors is 7 in the settings tab is that the short log sensor tables are already the biggest tables in the domoticz database. Setting the number of days to a higher value will have significant impact on database size and response times, specially around times backups are made.
If you really want to experiment with higher values you can do so by changing the setting directly in the database.
Code: Select all
cd <domoticz dir>
sudo cp domoticz.db domoticz.db_saved # just to make sure you have a backup
sudo sqlite3 domoticz.db "update preferences set nValue = nn where key = '5MinuteHistoryDays'"
Debian buster, bullseye on RPI-4, Intel NUC.
dz Beta, Z-Wave, RFLink, RFXtrx433e, P1, Youless, Hue, Yeelight, Xiaomi, MQTT
==>> dzVents wiki
dz Beta, Z-Wave, RFLink, RFXtrx433e, P1, Youless, Hue, Yeelight, Xiaomi, MQTT
==>> dzVents wiki
-
- Posts: 14
- Joined: Thursday 23 July 2020 11:47
- Target OS: Raspberry Pi / ODroid
- Domoticz version:
- Contact:
Re: how to modify the short log sensors more than 7
That’s a great answer, thanks. Reasons and solutions. Very helpful.waaren wrote: ↑Thursday 23 July 2020 13:55 The reason why the max days for short log sensors is 7 in the settings tab is that the short log sensor tables are already the biggest tables in the domoticz database. Setting the number of days to a higher value will have significant impact on database size and response times, specially around times backups are made.
If you really want to experiment with higher values you can do so by changing the setting directly in the database.
Code: Select all
cd <domoticz dir> sudo cp domoticz.db domoticz.db_saved # just to make sure you have a backup sudo sqlite3 domoticz.db "update preferences set nValue = nn where key = '5MinuteHistoryDays'"
If database size is the issue (as might impact Domoticz performance) maybe we could migrate records for over a week to a different database outside Domoticz. I’ll check this, as I want to have some data for future reference.
Thanks!
- waaren
- Posts: 6028
- Joined: Tuesday 03 January 2017 14:18
- Target OS: Linux
- Domoticz version: Beta
- Location: Netherlands
- Contact:
Re: how to modify the short log sensors more than 7
This should get you goingsoyelposeido wrote: ↑Thursday 23 July 2020 22:30 If database size is the issue (as might impact Domoticz performance) maybe we could migrate records for over a week to a different database outside Domoticz. I’ll check this, as I want to have some data for future reference.
Code: Select all
# ------------------ copy relevant table definitions from domoticz database
cd <domoticz dir>
sudo cp domoticz.db domoticz.db2 # workcopy
sudo sqlite3 # Create new database with schema of the short log tables
.open domoticz.db2
.out createHistory.sql
.schema MultiMeter
.schema Temperature
.schema Percentage
.schema UV
.schema Fan
.schema Rain
.schema Wind
.schema LightingLog
.schema Meter
.quit
#---------------------------- Create history database ( with added primary keys)
cd <domoticz dir>
sudo cp domoticz.db domoticz.db2 # workcopy
# add primary keys to table definitions
sudo sed -i 's/)))/)), PRIMARY KEY (deviceRowID, Date))/g' createHistory.sql
sudo sqlite3 #
.open dzHistory.db
.read createHistory.sql -- create database schema (only short log tables)
ATTACH DATABASE 'domoticz.db2' as source;
INSERT OR IGNORE INTO MultiMeter SELECT * FROM source.MultiMeter; -- initial copy of data
INSERT OR IGNORE INTO Temperature SELECT * FROM source.Temperature;
INSERT OR IGNORE INTO Percentage SELECT * FROM source.Percentage;
INSERT OR IGNORE INTO UV SELECT * FROM source.UV;
INSERT OR IGNORE INTO Fan SELECT * FROM source.Fan;
INSERT OR IGNORE INTO Rain SELECT * FROM source.Rain;
INSERT OR IGNORE INTO Wind SELECT * FROM source.Wind;
INSERT OR IGNORE INTO LightingLog SELECT * FROM source.LightingLog;
INSERT OR IGNORE INTO Meter SELECT * FROM source.Meter;
.quit
sudo rm createHistory.sql domoticz.db2 # cleanup
#------------------- daily update of short log sensor data to history database
cd <domoticz dir>
sudo cp domoticz.db domoticz.db2 # workcopy
sudo sqlite3
.open dzHistory.db
ATTACH DATABASE 'domoticz.db2' as source;
INSERT OR IGNORE INTO MultiMeter SELECT * FROM source.MultiMeter;
INSERT OR IGNORE INTO Temperature SELECT * FROM source.Temperature;
INSERT OR IGNORE INTO Percentage SELECT * FROM source.Percentage;
INSERT OR IGNORE INTO UV SELECT * FROM source.UV;
INSERT OR IGNORE INTO Fan SELECT * FROM source.Fan;
INSERT OR IGNORE INTO Rain SELECT * FROM source.Rain;
INSERT OR IGNORE INTO Wind SELECT * FROM source.Wind;
INSERT OR IGNORE INTO LightingLog SELECT * FROM source.LightingLog;
INSERT OR IGNORE INTO Meter SELECT * FROM source.Meter;
.quit
sudo rm domoticz.db2 # cleanup
Debian buster, bullseye on RPI-4, Intel NUC.
dz Beta, Z-Wave, RFLink, RFXtrx433e, P1, Youless, Hue, Yeelight, Xiaomi, MQTT
==>> dzVents wiki
dz Beta, Z-Wave, RFLink, RFXtrx433e, P1, Youless, Hue, Yeelight, Xiaomi, MQTT
==>> dzVents wiki
Re: how to modify the short log sensors more than 7
NICE
I did put it together in a bash script.
@waaren Just a question. Why is in the domoticz database the "PRIMARY KEY (deviceRowID, Date)" not present in the long log sensor data?
Over the years I got a lot of duplicated records in those tables.



I did put it together in a bash script.
Code: Select all
#!/bin/bash
HISTORY_DB="/home/pi/domoticz/dzHistory.db"
# ------------------ copy relevant table definitions from domoticz database
cd /home/pi/domoticz
if [ ! -f $HISTORY_DB ]; then
sudo cp domoticz.db domoticz.db2 # workcopy
# Create new database with schema of the short log tables
sudo sqlite3 \
-cmd ".open domoticz.db2" \
".out createHistory.sql" \
".schema MultiMeter" \
".schema Temperature" \
".schema Percentage" \
".schema UV" \
".schema Fan" \
".schema Rain" \
".schema Wind" \
".schema LightingLog" \
".schema Meter" \
".quit" > createHistory.sql
#---------------------------- Create history database ( with added primary keys)
# add primary keys to table definitions
sudo sed -i 's/)))/)), PRIMARY KEY (deviceRowID, Date))/g' createHistory.sql
# create database schema (only short log tables)
sudo sqlite3 dzHistory.db \
-cmd ".read createHistory.sql" \
".quit"
sudo sqlite3 $HISTORY_DB "ATTACH DATABASE 'domoticz.db2' as source;INSERT OR IGNORE INTO MultiMeter SELECT * FROM so
urce.MultiMeter;INSERT OR IGNORE INTO Temperature SELECT * FROM source.Temperature;INSERT OR IGNORE INTO Percentage SELE
CT * FROM source.Percentage;INSERT OR IGNORE INTO UV SELECT * FROM source.UV;INSERT OR IGNORE INTO Fan SELECT * FROM sou
rce.Fan;INSERT OR IGNORE INTO Rain SELECT * FROM source.Rain;INSERT OR IGNORE INTO Wind SELECT * FROM source.Wind;INSERT
OR IGNORE INTO LightingLog SELECT * FROM source.LightingLog;INSERT OR IGNORE INTO Meter SELECT * FROM source.Meter;" \
-cmd ".quit"
sudo rm createHistory.sql domoticz.db2 # cleanup
else
#------------------- daily update of short log sensor data to history database
sudo cp domoticz.db domoticz.db2 # workcopy
sudo sqlite3 $HISTORY_DB "ATTACH DATABASE 'domoticz.db2' as source;INSERT OR IGNORE INTO MultiMeter SELECT * FROM so
urce.MultiMeter;INSERT OR IGNORE INTO Temperature SELECT * FROM source.Temperature;INSERT OR IGNORE INTO Percentage SELE
CT * FROM source.Percentage;INSERT OR IGNORE INTO UV SELECT * FROM source.UV;INSERT OR IGNORE INTO Fan SELECT * FROM sou
rce.Fan;INSERT OR IGNORE INTO Rain SELECT * FROM source.Rain;INSERT OR IGNORE INTO Wind SELECT * FROM source.Wind;INSERT
OR IGNORE INTO LightingLog SELECT * FROM source.LightingLog;INSERT OR IGNORE INTO Meter SELECT * FROM source.Meter;" \
-cmd ".quit"
sudo rm domoticz.db2 # cleanup
Over the years I got a lot of duplicated records in those tables.
- waaren
- Posts: 6028
- Joined: Tuesday 03 January 2017 14:18
- Target OS: Linux
- Domoticz version: Beta
- Location: Netherlands
- Contact:
Re: how to modify the short log sensors more than 7
I don't know why it was not implemented from the start. Maybe too much trust in the application code thinking duplicates would never occur ? Maybe afraid for performance degradation ? (there is some (but limited) performance impact when inserting / updating records).
The problem now is that sqlite does not allow altering the schema of an existing table. This combined with the potential large tables prevent introducing it.
Adding such a Primary key in a sqlite table requires the table to be copied to a helper table, deleting- and recreating it with the altered schema and copy the data back from the helper table.
I did this for the (small) Preferences table in build 12202 commit 9cefa7a8e but it is too risky to do this for potential large tables. It might just takes a very long time and even crash if there is not enough free space on the system and needs to be done on the first start of the new version.
Luckily you can delete duplicates from these tables with a relatively simple command per table.
Code: Select all
cd <domoticz dir>
sudo service domoticz stop
sudo cp domoticz.db domoticz.db_safe
sudo sqlite3 domoticz.db
delete from Fan_Calendar where rowid not in (select min(rowid) from Fan_Calendar group by devicerowid, date);select changes();
delete from Meter_Calendar where rowid not in (select min(rowid) from Meter_Calendar group by devicerowid, date);select changes();
delete from MultiMeter_Calendar where rowid not in (select min(rowid) from MultiMeter_Calendar group by devicerowid, date);select changes();
delete from Percentage_Calendar where rowid not in (select min(rowid) from Percentage_Calendar group by devicerowid, date);select changes();
delete from Rain_Calendar where rowid not in (select min(rowid) from Rain_Calendar group by devicerowid, date);select changes();
delete from Temperature_Calendar where rowid not in (select min(rowid) from Temperature_Calendar group by devicerowid, date);select changes();
delete from UV_Calendar where rowid not in (select min(rowid) from UV_Calendar group by devicerowid, date);select changes();
delete from Wind_Calendar where rowid not in (select min(rowid) from Wind_Calendar group by devicerowid, date);select changes();
delete from LightingLog where rowid not in (select min(rowid) from LightingLog group by devicerowid, date);select changes();
.quit
sudo service domoticz start
Debian buster, bullseye on RPI-4, Intel NUC.
dz Beta, Z-Wave, RFLink, RFXtrx433e, P1, Youless, Hue, Yeelight, Xiaomi, MQTT
==>> dzVents wiki
dz Beta, Z-Wave, RFLink, RFXtrx433e, P1, Youless, Hue, Yeelight, Xiaomi, MQTT
==>> dzVents wiki
Re: how to modify the short log sensors more than 7
Tnx. I already did some deleting but also did some editing because I noticed that some duplicates had the next day missing.
Probably caused by a problem with the Raspberry Pi's clock at the boot process.
Probably caused by a problem with the Raspberry Pi's clock at the boot process.
-
- Posts: 5
- Joined: Sunday 25 February 2024 16:05
- Target OS: Raspberry Pi / ODroid
- Domoticz version: 2024.7
- Location: Belgium
- Contact:
Re: how to modify the short log sensors more than 7
It works for a while but for an unknown reason Domoticz changes the nn value to 0 after a while and an error message is generated every 5 minutes. Renentering the sqlite command and it works again for a while.waaren wrote: ↑Thursday 23 July 2020 13:55The reason why the max days for short log sensors is 7 in the settings tab is that the short log sensor tables are already the biggest tables in the domoticz database. Setting the number of days to a higher value will have significant impact on database size and response times, specially around times backups are made.
If you really want to experiment with higher values you can do so by changing the setting directly in the database.
Code: Select all
cd <domoticz dir> sudo cp domoticz.db domoticz.db_saved # just to make sure you have a backup sudo sqlite3 domoticz.db "update preferences set nValue = nn where key = '5MinuteHistoryDays'"
- waltervl
- Posts: 5389
- Joined: Monday 28 January 2019 18:48
- Target OS: Linux
- Domoticz version: 2024.7
- Location: NL
- Contact:
Re: how to modify the short log sensors more than 7
As expected.
If you want a more robust solution send your short log data directly to an influx database and do the graphing from there.
See the wiki page https://wiki.domoticz.com/Influxdb
If you want a more robust solution send your short log data directly to an influx database and do the graphing from there.
See the wiki page https://wiki.domoticz.com/Influxdb
Domoticz running on Udoo X86 (on Ubuntu)
Devices/plugins: ZigbeeforDomoticz (with Xiaomi, Ikea, Tuya devices), Nefit Easy, Midea Airco, Omnik Solar, Goodwe Solar
Devices/plugins: ZigbeeforDomoticz (with Xiaomi, Ikea, Tuya devices), Nefit Easy, Midea Airco, Omnik Solar, Goodwe Solar
Who is online
Users browsing this forum: No registered users and 1 guest