Save detailed data over 24 hours

Python and python framework
Post Reply
albercola
Posts: 10
Joined: Monday 19 February 2018 19:15
Target OS: Windows
Domoticz version:
Contact:

Save detailed data over 24 hours

Post by albercola » Wednesday 04 April 2018 23:11

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

User avatar
waaren
Posts: 1201
Joined: Tuesday 03 January 2017 15:18
Target OS: Raspberry Pi
Domoticz version: Beta
Location: Netherlands
Contact:

Re: Save detailed data over 24 hours

Post by waaren » Thursday 05 April 2018 1:14

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!
Raspberry (debian stretch via berryboot on Synology DS916+) , Domoticz (almost) latest Beta, , dzVents 2.6, RFLink, RFXtrx433e, P1, Youless, Harmony, Hue, Yeelight, Xiaomi, HomeWizard, Zwave, Amazon echo

CaesarPL
Posts: 129
Joined: Tuesday 08 November 2016 15:03
Target OS: Raspberry Pi
Domoticz version:
Location: Poland
Contact:

Re: Save detailed data over 24 hours

Post by CaesarPL » Thursday 05 April 2018 9:54

Why don't you use Influxdb and Grafana?
HW:Raspberry PiB+,Pi3,PiB,RFLink,FA20RF,DCS-930L,DCS-935L,Clarus switches,RF Temp sensors,Owl Micro+,Mi Flora,Kerui,Yeelight,Xiaomi,Broadlink RMM3,Alexa
SF:Jessie,Dmtcz 3.8153,Logitech Media srv,PiCorePlayers,Dashticz,Max2Play,InfluxDB,Grafana,HABridge

albercola
Posts: 10
Joined: Monday 19 February 2018 19:15
Target OS: Windows
Domoticz version:
Contact:

Re: Save detailed data over 24 hours

Post by albercola » Thursday 05 April 2018 16:44

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
Posts: 4
Joined: Wednesday 17 October 2018 13:32
Target OS: Raspberry Pi
Domoticz version:
Contact:

Re: Save detailed data over 24 hours

Post by Wienen » Friday 09 November 2018 14:53

albercola wrote: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 Image


Verzonden vanaf mijn iPhone met Tapatalk

Post Reply

Who is online

Users browsing this forum: No registered users and 3 guests