Page 1 of 1
Read temp value 15 min ago
Posted: Saturday 07 November 2015 11:41
by Minglarn
Hi folks!
I need to read the stored temp value for a specific temperature senor from domoticz.
I'm reprogramming my front-page to use the domoticz as a base for temp values and to read status from a couple of switches.
- 000.JPG (26.3 KiB) Viewed 2592 times
As seen in the picture above I have a "trending" arrow for outside temperature. I'ts based on comparison of outside temperature 30 min ago. But it reads the value from a WAN stored database and I dont want that.
Is it possible to read the stored value for a specific time from Domotics?
Re: Read temp value 15 min ago
Posted: Saturday 07 November 2015 13:33
by Egregius
You could read it directly from the SQLite database.
Re: Read temp value 15 min ago
Posted: Saturday 07 November 2015 13:36
by Minglarn
Ok, any info on how to do it?
Using PHP to do my frontpage so it would be perfect if its doable from PHP.
EDIT: did a search on "SQLite" and see now that domoticz has to be stopped first... No good.
Re: Read temp value 15 min ago
Posted: Saturday 07 November 2015 13:46
by Egregius
Something quick:
Code: Select all
<?php
$dbd = new SQLite3('/home/pi/domoticz/domoticz.db');
$sensor = 38; //IDX of the thermometer
$sql = "SELECT Date, Temperature FROM Temperature WHERE DeviceRowID = $sensor ORDER BY Date DESC";
if(!$result = $dbd->query($sql)){ echo('There was an error running the query [' . $dbd->error . ']');}
$times = array();
while($row = $result->fetchArray()){
array_push($times, array('Date' => $row['Date'], 'Temperature' => $row['Temperature']));
}
That gives you a array $times() with all values for that thermometer. Add a limit 0,5 to only take last 5. Or whatever query you like.
I never stop domoticz for reading the database. Reading is the only thing I do and never had a problem with it.
I use above for some custom temperature graphs with the associated setpoints in 1 graph.
The complete code can be viewed at
https://github.com/Egregius/PHP-Custom- ... r/temp.php
Re: Read temp value 15 min ago
Posted: Saturday 07 November 2015 14:36
by SweetPants
Minglarn wrote:did a search on "SQLite" and see now that domoticz has to be stopped first... No good.
SELECT should work on a SQLite database allthough it is not supported!!!
Re: Read temp value 15 min ago
Posted: Saturday 07 November 2015 15:04
by Minglarn
Egregius wrote:Something quick:
Code: Select all
<?php
$dbd = new SQLite3('/home/pi/domoticz/domoticz.db');
$sensor = 38; //IDX of the thermometer
$sql = "SELECT Date, Temperature FROM Temperature WHERE DeviceRowID = $sensor ORDER BY Date DESC";
if(!$result = $dbd->query($sql)){ echo('There was an error running the query [' . $dbd->error . ']');}
$times = array();
while($row = $result->fetchArray()){
array_push($times, array('Date' => $row['Date'], 'Temperature' => $row['Temperature']));
}
That gives you a array $times() with all values for that thermometer. Add a limit 0,5 to only take last 5. Or whatever query you like.
I never stop domoticz for reading the database. Reading is the only thing I do and never had a problem with it.
I use above for some custom temperature graphs with the associated setpoints in 1 graph.
The complete code can be viewed at
https://github.com/Egregius/PHP-Custom- ... r/temp.php
Thanks for the code, but ain't there a easier way to get the values or?
Sent from space using an app.
Re: Read temp value 15 min ago
Posted: Saturday 07 November 2015 15:32
by Egregius
It's quite easy to pull one value:
Code: Select all
<?php
$dbd = new SQLite3('/home/pi/domoticz/domoticz.db');
$sql = "SELECT Temperature FROM Temperature WHERE DeviceRowID = 38 ORDER BY Date DESC limit 2,1";
if(!$result = $dbd->query($sql)){ echo('There was an error running the query [' . $dbd->error . ']');}
$row = $result->fetchArray();
echo $row['Temperature'];
Maybe average of last 3 values could be interesting for you:
Code: Select all
<?php
$dbd = new SQLite3('/home/pi/domoticz/domoticz.db');
$sql = "SELECT avg(Temperature) as Temperature FROM Temperature WHERE DeviceRowID = 38 ORDER BY Date DESC limit 0,3";
if(!$result = $dbd->query($sql)){ echo('There was an error running the query [' . $dbd->error . ']');}
$row = $result->fetchArray();
echo $row['Temperature'];
Re: Read temp value 15 min ago
Posted: Saturday 07 November 2015 18:15
by Minglarn
Thanks!! I really appreciate your support...
So how do I gain access to domoticz database? Do I need to install SQL Lite?
Re: Read temp value 15 min ago
Posted: Sunday 08 November 2015 7:38
by Egregius
Did you try the code above? Does it give an error?
I can't remember if I installed SQLite3 myself or that it was already installed in the Rpi image.
Otherwise I would think "sudo apt-get install sqlite3" should do it.
Re: Read temp value 15 min ago
Posted: Sunday 08 November 2015 20:30
by Minglarn
Having trouble to get PHP script running on domoticz... investigating at the moment.
Re: Read temp value 15 min ago
Posted: Tuesday 10 November 2015 19:44
by Minglarn
Hmm. Seems that I have to install Apache and php.
Sent from space using an app.
Re: Read temp value 15 min ago
Posted: Tuesday 10 November 2015 22:31
by Egregius
I guessed you already had those because you asked for a php script.
Apache runs fine on the Rpi
