Read temp value 15 min ago

[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 2589 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?

[Egregius]

You could read it directly from the SQLite database.

[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.

[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

[SweetPants]

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!!!

[Minglarn]

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.

[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'];

[Minglarn]

Thanks!! I really appreciate your support...
So how do I gain access to domoticz database? Do I need to install SQL Lite?

[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.

[Minglarn]

Having trouble to get PHP script running on domoticz... investigating at the moment.

[Minglarn]

Hmm. Seems that I have to install Apache and php.


Sent from space using an app.

[Egregius]

I guessed you already had those because you asked for a php script.
Apache runs fine on the Rpi