Domoticz

Posted: **Wednesday 06 September 2017 8:51**

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.

Posted: **Wednesday 06 September 2017 13:07**

You can't

Posted: **Wednesday 06 September 2017 13:48**

thank you for your response

Posted: **Thursday 23 July 2020 11:51**

Still can't?

Posted: **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'"

Posted: **Thursday 23 July 2020 22:30**

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!

Posted: **Friday 24 July 2020 1:29**

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

Posted: **Friday 24 July 2020 10:20**

NICE

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.

Posted: **Friday 24 July 2020 11:03**

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

Posted: **Friday 24 July 2020 11:38**

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.

Posted: **Monday 02 December 2024 18:15**

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.

Posted: **Monday 02 December 2024 19:04**

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

how to modify the short log sensors more than 7

how to modify the short log sensors more than 7

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

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

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

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

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

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

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

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

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

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

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