p1 reading download more data

[lsp242]

Hi,

in domoticz P1 reading, you can download to xls or csv. Is it possible to get more dayly data out off the Pi table written by the P1.
Now i get only 24 ours in the day reading.

[waaren]

Hi,

in domoticz P1 reading, you can download to xls or csv. Is it possible to get more dayly data out off the Pi table written by the P1.
Now i get only 24 ours in the day reading.

Not directly with a per 5 minute granularity. This granular data is only kept for 24 - 168 hours. (defined in settings)
if you want to keep this kind of details you will have to retrieve it daily and store it elsewhere.

I do something similar (get P1, water and weather information from HomeWizard) with a cron job and store it in a separate database.

You can retrieve P1 data (Power and Gas) from the domoticz database with sqlite3

Code: Select all

-- 1 row per 5 minutes Power
select * from MultiMeter where DeviceRowID in (select ID from DeviceStatus where HardwareID in (select ID from Hardware where Name = "P1"));
-- 1 row per 5 minutes Gas
select * from Meter where DeviceRowID in (select ID from DeviceStatus where HardwareID in (select ID from Hardware where Name = "P1"));
   
-- 1 row per day  Power
select * from MultiMeter_Calendar where DeviceRowID in (select ID from DeviceStatus where HardwareID in (select ID from Hardware where Name = "P1"));
-- 1 row per day  Gas
select * from Meter_Calendar where DeviceRowID in (select ID from DeviceStatus where HardwareID in (select ID from Hardware where Name = "P1"));
   
   

[lsp242]

Hi,

in domoticz P1 reading, you can download to xls or csv. Is it possible to get more dayly data out off the Pi table written by the P1.
Now i get only 24 ours in the day reading.

Not directly with a per 5 minute granularity. This granular data is only kept for 24 hours.
if you want to keep this kind of details you will have to retrieve it daily and store it elsewhere.

I do something similar (get P1, water and weather information from HomeWizard) with a cron job and store it in a separate database.

You can retrieve P1 data (Power and Gas) from the domoticz database with sqlite3

Code: Select all

-- 1 row per 5 minutes Power
select * from MultiMeter where DeviceRowID in (select ID from DeviceStatus where HardwareID in (select ID from Hardware where Name = "P1"));
-- 1 row per 5 minutes Gas
select * from Meter where DeviceRowID in (select ID from DeviceStatus where HardwareID in (select ID from Hardware where Name = "P1"));
   
-- 1 row per day  Power
select * from MultiMeter_Calendar where DeviceRowID in (select ID from DeviceStatus where HardwareID in (select ID from Hardware where Name = "P1"));
-- 1 row per day  Gas
select * from Meter_Calendar where DeviceRowID in (select ID from DeviceStatus where HardwareID in (select ID from Hardware where Name = "P1"));
   
   

Thnx, can you put me in the direction to find how to install sqlite on pi and were to edit code? I'm just a beginner

[waaren]

!! before you are going to play with the database ensure you have a recent backup !!

https://sqlite.org/docs.html

Code: Select all

sudo apt install sqlite3        

cd domoticzdir
sudo sqlite3 domoticz.db
.header on
.tables

[lsp242]

thnx i will look into it.

be sure don't i have toe install php7.0-sqlite on de Pi? I read on google i need that, but when i do "sudo apt-get install php7.0-sqlite3" i've got the error message "E: Kan pakket php7.0-sqlite3 niet vinden" in english "E:can't find package php7.0-sqlite3"

When i only use php-sqlite i've got the same problem.