Store temperature data from Domoticz in mySQL for historical analyses
This PHP script will retrieve temperature data from Domoticz and store it in a MySQL database. This way you can keep detailed information for more than 7 days, which is the limit for the short log in Domoticz.
Detailed temperatures are stored in the table temp_day. Daily minimal, average and maximum temperatures in temp_month.
The script automatically adds columns for each thermometer you put in the devices array. Column names are set to the name of the device in Domoticz. This means that if you ever replace a thermometer you only have to give it the same name and change the idx in the script.
All you need to do is create a database and adjust the items for each define line, the script does all the rest.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters. Learn more about bidirectional Unicode characters
| <?php | |
| define('sqlserver','127.0.0.1'); | |
| define('sqluser','domotica'); | |
| define('sqlpassword','domotica'); | |
| define('sqldatabase','domotica'); | |
| define('updatedatabase',true);//Set to false once the database is created, set to true to add devices | |
| define('domoticzip','127.0.0.1'); | |
| define('domoticzport','8080'); | |
| define('numberofhours',1);//Defines how many hours of detailed data is updated in _day table. Set to a high number for initial run. | |
| define('numberofdays',1);//Defindes how many days of min,avg and max is updated in _month table. Set to a high number for initial run. | |
| define('devices',array(231,236,238,573,588,1260,1475));//IDXs of temperature devices | |
| define('sleep',10000);//Defines how many microseconds of sleep there should be between queries, can be usefull on low power devices | |
| if (updatedatabase) { | |
| createupdatedatabase(); | |
| } | |
| $db=new mysqli(sqlserver,sqluser,sqlpassword,sqldatabase); | |
| if ($db->connect_errno>0) { | |
| die('Unable to connect to database ['.$db->connect_error.']'); | |
| } | |
| foreach (devices as $idx) { | |
| $device=json_decode( | |
| file_get_contents( | |
| 'http://'.domoticzip.':'.domoticzport.'/json.htm?type=devices&rid='.$idx), | |
| true | |
| ); | |
| if (!empty($device['result'][0]['Name'])) { | |
| $name=$device['result'][0]['Name']; | |
| $day=json_decode( | |
| file_get_contents( | |
| 'http://'.domoticzip.':'.domoticzport.'/json.htm?type=graph&sensor=temp&idx='.$idx.'&range=day'), | |
| true | |
| ); | |
| if (!empty($day['result'])) { | |
| foreach ($day['result'] as $i) { | |
| $stamp=$i['d']; | |
| if(strtotime($stamp)>time()-(3600*numberofhours)){ | |
| $temp=$i['te']; | |
| $query="INSERT INTO `temp_day` (`stamp`,`$name`) | |
| VALUES ('$stamp','$temp') | |
| ON DUPLICATE KEY UPDATE `$name`='$temp'"; | |
| echo $query.'<br>'; | |
| if (!$result=$db->query($query)) { | |
| echo('There was an error running the query "'.$query.'" - '.$db->error); | |
| } | |
| usleep(sleep); | |
| } | |
| } | |
| } | |
| usleep(sleep); | |
| $month=json_decode( | |
| file_get_contents( | |
| 'http://'.domoticzip.':'.domoticzport.'/json.htm?type=graph&sensor=temp&idx='.$idx.'&range=month'), | |
| true | |
| ); | |
| if (!empty($month['result'])) { | |
| foreach ($month['result'] as $i) { | |
| $stamp=$i['d']; | |
| if (strtotime($stamp)>time()-(86400*numberofdays)) { | |
| $min=$i['tm']; | |
| $avg=$i['ta']; | |
| $max=$i['te']; | |
| $cmin=$name.'_min'; | |
| $cavg=$name.'_avg'; | |
| $cmax=$name.'_max'; | |
| $query="INSERT INTO `temp_month` (`stamp`,`$cmin`,`$cavg`,`$cmax`) | |
| VALUES ('$stamp','$min','$avg','$max') | |
| ON DUPLICATE KEY UPDATE `$cmin`='$min',`$cavg`='$avg',`$cmax`='$max'"; | |
| echo $query.'<br>'; | |
| if (!$result=$db->query($query)) { | |
| echo('There was an error running the query "'.$query.'" - '.$db->error); | |
| } | |
| usleep(sleep); | |
| } | |
| } | |
| } | |
| usleep(sleep); | |
| } | |
| } | |
| function createupdatedatabase() | |
| { | |
| $db=new mysqli(sqlserver,sqluser,sqlpassword,sqldatabase); | |
| if ($db->connect_errno>0){ | |
| die('Unable to connect to database ['.$db->connect_error.']'); | |
| } | |
| $sqldatabase=sqldatabase; | |
| $result=mysqli_fetch_assoc( | |
| mysqli_query( | |
| $db, | |
| "SELECT count(*) as count | |
| FROM information_schema.TABLES | |
| WHERE (TABLE_SCHEMA = '$sqldatabase') | |
| AND (TABLE_NAME = 'temp_day')" | |
| ) | |
| ); | |
| if ($result['count']==0) { | |
| $query="CREATE TABLE temp_day ( | |
| stamp datetime PRIMARY KEY | |
| )"; | |
| if (!$result=$db->query($query)) { | |
| die('There was an error running the query ['.$query.' - '.$db->error.']'); | |
| } | |
| } | |
| foreach (devices as $idx) { | |
| $device=json_decode( | |
| file_get_contents( | |
| 'http://'.domoticzip.':'.domoticzport.'/json.htm?type=devices&rid='.$idx), | |
| true | |
| ); | |
| if (!empty($device['result'][0]['Name'])) { | |
| $name=$device['result'][0]['Name']; | |
| $result=mysqli_fetch_assoc( | |
| mysqli_query( | |
| $db, | |
| "SELECT COLUMN_TYPE | |
| FROM INFORMATION_SCHEMA.COLUMNS | |
| WHERE table_name = 'temp_day' | |
| AND COLUMN_NAME = '$name';" | |
| ) | |
| ); | |
| if ($result['COLUMN_TYPE']!='float(3,1)') { | |
| $query="ALTER TABLE `temp_day` ADD `$name` FLOAT(3,1) NULL;"; | |
| if (!$result=$db->query($query)) { | |
| die('There was an error running the query ['.$query.' - '.$db->error.']'); | |
| } | |
| } | |
| } | |
| } | |
| $result=mysqli_fetch_assoc( | |
| mysqli_query( | |
| $db, | |
| "SELECT count(*) as count | |
| FROM information_schema.TABLES | |
| WHERE (TABLE_SCHEMA = '$sqldatabase') | |
| AND (TABLE_NAME = 'temp_month')" | |
| ) | |
| ); | |
| if ($result['count']==0) { | |
| $query="CREATE TABLE temp_month ( | |
| stamp date PRIMARY KEY | |
| )"; | |
| if (!$result=$db->query($query)) { | |
| die('There was an error running the query ['.$query.' - '.$db->error.']'); | |
| } | |
| } | |
| foreach (devices as $idx) { | |
| $device=json_decode( | |
| file_get_contents( | |
| 'http://'.domoticzip.':'.domoticzport.'/json.htm?type=devices&rid='.$idx), | |
| true | |
| ); | |
| if (!empty($device['result'][0]['Name'])) { | |
| $name=$device['result'][0]['Name']; | |
| $tables=array($name.'_min',$name.'_avg',$name.'_max'); | |
| foreach ($tables as $table) { | |
| $result=mysqli_fetch_assoc( | |
| mysqli_query( | |
| $db, | |
| "SELECT COLUMN_TYPE | |
| FROM INFORMATION_SCHEMA.COLUMNS | |
| WHERE table_name = 'temp_month' | |
| AND COLUMN_NAME = '$table';" | |
| ) | |
| ); | |
| if ($result['COLUMN_TYPE']!='float(3,1)') { | |
| $query="ALTER TABLE `temp_month` ADD `$table` FLOAT(3,1) NULL;"; | |
| if (!$result=$db->query($query)) { | |
| die('There was an error running the query ['.$query.' - '.$db->error.']'); | |
| } | |
| } | |
| } | |
| } | |
| } | |
| } | |
| ?> |
2 reacties
Geef een reactie
Deze site gebruikt Akismet om spam te verminderen. Bekijk hoe je reactie-gegevens worden verwerkt.
Hello,
I’ve tried this code.
I’m running php on a synology, so i have to access the database of the raspberry pi where the data is stored.
Now i use a username and pasword.
let me know how i can access the database on the pi with a username ans password to load it into the sql on the synology
Hi Stef,
I think that should be something like http://username:password@‘.domoticzip.’:’.domoticzport.’/json.htm?type=devices&rid=’.$idx on line 23.
Or add your Synology’s IP address to the Local Networks (no username/password) of the Domoticz settings page to bypass authentication.