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!
