Save detailed data over 24 hours

[albercola]

Hi everyone,
I have a Raspberry 3 that I use to record different temperature of a boiler of mine.I can't find a detailed/basic guide regarding how to save in a separate database ALL daily data (with 5 min interval) before they are overwritten in the day after. Can somebody help me? thanks in advance

[waaren]

cd "domoticzdir"

Code: Select all

# open domoticz database
sudo sqlite3 domoticz.db

-- file to receive output
.output t_schema 

-- send schema of temperature table to t_schema
.schema temperature
.quit

edit t_schema (to give table different name and add a primary key. This will prevent double entries)
from: (schema of table in domoticz.db)

Code: Select all

CREATE TABLE [Temperature] ([DeviceRowID] BIGINT(10) NOT NULL, [Temperature] FLOAT NOT NULL, [Chill] FLOAT DEFAULT 0, [Humidity] INTEGER DEFAULT 0, [Barometer] INTEGE
R DEFAULT 0, [DewPoint] FLOAT DEFAULT 0, [SetPoint] FLOAT DEFAULT 0, [Date] DATETIME DEFAULT (datetime('now','localtime')));
CREATE INDEX t_id_idx        on Temperature(DeviceRowID);
CREATE INDEX t_id_date_idx   on Temperature(DeviceRowID, Date);

to: (schema of table in historyTemperatures.db)

Code: Select all

CREATE TABLE [HTemperature] ([DeviceRowID] BIGINT(10) NOT NULL, [Temperature] FLOAT NOT NULL, [Chill] FLOAT DEFAULT 0, [Humidity] INTEGER DEFAULT 0, [Barometer] INTEGER DEFAULT 0, [DewPoint] FLOAT DEFAULT 0, [SetPoint] FLOAT DEFAULT 0, [Date] DATETIME DEFAULT (datetime('now','localtime')),PRIMARY KEY (DeviceRowID, Date));
CREATE INDEX t_id_idx        on HTemperature(DeviceRowID);
CREATE INDEX t_id_date_idx   on HTemperature(DeviceRowID, Date);

create new database with one table

Code: Select all

sudo sqlite3 historyTemperatures.db < t_schema

create file updateHistory.sql

Code: Select all

attach 'historyTemperatures.db' as ht;
select count(*) as entries from ht.HTemperature;
insert or ignore into ht.HTemperature select * from main.Temperature;
select count(*) as entries from ht.HTemperature;

create a cronjob that must execute at least every 24 hours but to be on the safe side, I advise to run it more frequent

Code: Select all

#!/bin/bash
#
#
cd "domoticzdir"
result=$(sudo sqlite3 domoticz.db < updateHistory.sql)

beforeUpdate=$(echo $result | cut -d ' ' -f1 )
afterUpdate=$(echo $result | cut -d ' ' -f2 )

echo Before updating temperatureHistory the table contained $beforeUpdate records.
echo After  updating temperatureHistory the table contains $afterUpdate records.

Please note that if the domoticz developers decide to change the schema of the Temperature table, you have to change the HTemperature table alike.

Have Fun!

[CaesarPL]

Why don't you use Influxdb and Grafana?

[albercola]

thank you to Waaren and CaesarPL: I'll try in the WE. I hope to solve my problem with a python script that is a bit easier for me. But I wonder why in a so sophisticated application like Domoticz there is not an option to choose if overwrite or enlarge the db. Anyway I'll update you about my progress. Thanks again!

[Wienen]

thank you to Waaren and CaesarPL: I'll try in the WE. I hope to solve my problem with a python script that is a bit easier for me. But I wonder why in a so sophisticated application like Domoticz there is not an option to choose if overwrite or enlarge the db. Anyway I'll update you about my progress. Thanks again!

@albercola; did you find a way to accomplish this? I want the same thing only then for my gas and energy consumption.

Hope you can point me in the right direction


Verzonden vanaf mijn iPhone met Tapatalk