how to modify the short log sensors more than 7 Topic is solved

Topics (not sure which fora)
when not sure where to post, post here and mods will move it to right forum.

Moderators: leecollings, remb0

Post Reply
joelrigo
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

Post by joelrigo »

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.
SweetPants

Re: how to modify the short log sensors more than 7

Post by SweetPants »

You can't
joelrigo
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

Post by joelrigo »

thank you for your response
soyelposeido
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

Post by soyelposeido »

Still can't?
User avatar
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

Post by waaren »

soyelposeido wrote: Thursday 23 July 2020 11:51 Still can't?
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
soyelposeido
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

Post by soyelposeido »

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'"
That’s a great answer, thanks. Reasons and solutions. Very helpful.

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!
User avatar
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

Post by waaren »

soyelposeido 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.
This should get you going

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
EddyG
Posts: 1042
Joined: Monday 02 November 2015 5:54
Target OS: -
Domoticz version:

Re: how to modify the short log sensors more than 7

Post by EddyG »

NICE :D :D :D
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

@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.
User avatar
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

Post by waaren »

EddyG wrote: Friday 24 July 2020 10:20 @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 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
EddyG
Posts: 1042
Joined: Monday 02 November 2015 5:54
Target OS: -
Domoticz version:

Re: how to modify the short log sensors more than 7

Post by EddyG »

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.
Molenman
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

Post by Molenman »

waaren wrote: Thursday 23 July 2020 13:55
soyelposeido wrote: Thursday 23 July 2020 11:51 Still can't?
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'"
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.
User avatar
waltervl
Posts: 5397
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

Post by waltervl »

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
Domoticz running on Udoo X86 (on Ubuntu)
Devices/plugins: ZigbeeforDomoticz (with Xiaomi, Ikea, Tuya devices), Nefit Easy, Midea Airco, Omnik Solar, Goodwe Solar
Post Reply

Who is online

Users browsing this forum: No registered users and 1 guest