{"id":1492,"date":"2013-03-05T17:53:49","date_gmt":"2013-03-05T15:53:49","guid":{"rendered":"http:\/\/sickel.net\/blogg\/?p=1492"},"modified":"2013-03-18T23:08:12","modified_gmt":"2013-03-18T22:08:12","slug":"storing-temperature-data-in-postgresql","status":"publish","type":"post","link":"https:\/\/sickel.net\/blogg\/?p=1492","title":{"rendered":"Storing temperature data in postgresql"},"content":{"rendered":"<p>An arduino board is running as a <a href=\"http:\/\/sickel.net\/blogg\/?p=1425\">web server<\/a> serving temperatures read out from some DS18B20 sensors. The data are stored in a postgresql database. The data table has been defined as:<\/p>\n<pre>\r\ncreate table temps(\r\n   id serial primary key,\r\n   termid integer,\r\n   temp double presicion not null,\r\n   sensoraddr character varying(20),\r\n   datetime  timestamp with time zone default now()\r\n);\r\n<\/pre>\n<p>A php-script is set upp to poll the arduino and fetch data. It sends data as json, described <a href=\"http:\/\/sickel.net\/blogg\/?p=1425\">here<\/a>. <\/p>\n<pre>\r\n&lt;?php\r\n$server=\"192.168.0.177\"; \/\/ Must be set up to match the arduino's IP.\r\n$url=\"${server}\/json\";\r\n$database='wdb';\r\n$pgserver='localhost';   \/\/ postgres server\r\n$username='.....'; \/\/ postgres user name\r\n$password='.....'; \/\/ postgres password\r\n$dbtype='pgsql';\r\ntry{\r\n    $connectstring=$dbtype.':host='.$pgserver.';dbname='.$database;\r\n    $dbh = new PDO($connectstring, $username, $password);\r\n    if($dbtype=='pgsql'){\r\n      $dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);\r\n    }\r\n\r\n  }\r\n  catch(PDOException $e){\r\n    $message=$e->getMessage(); \r\n    exit( \"\r\n\r\nCannot connect - $message\r\n\");\r\n  }\r\n\r\n$fp = fsockopen($server, 80, $errno, $errstr, 30);\r\n$data='';\r\nif (!$fp) {\r\n    echo \"$errstr ($errno)\r\n\\n\";\r\n} else {\r\n    $out = \"GET \/json HTTP\/1.1\\r\\n\";\r\n    $out .= \"Host: $server\\r\\n\";\r\n    $out .= \"Connection: Close\\r\\n\\r\\n\";\r\n    fwrite($fp, $out);\r\n    while (!feof($fp)) {\r\n        $data .= fgets($fp, 128);\r\n    }\r\n    fclose($fp);\r\n}\r\n$data=split(\"\\n\",$data); \/\/ cuts of the headers\r\n$jsondata=json_decode($data[3]);\r\n$sql=\"insert into temps(temp,sensoraddr)values(?,?)\";\r\n$sh=$dbh->prepare($sql);\r\nfor($i=0;$i<count ($jsondata->temp);$i++){\r\n        $sh->execute(array($jsondata->temp[$i],$jsondata->address[$i]));\r\n}\r\n?&gt;\r\n<\/count><\/pre>\n<p>This can be trigged from some event, or it may be run periodically. To make it run once every 15 minutes, save the php script as &#8216;fetchtemp.php&#8217; and make the following shell-script in the same directory:<\/p>\n<pre>\r\n#!\/bin\/bash\r\nwhile true; do\r\nphp fetchtemp.php\r\nsleep 900\r\ndone\r\n<\/pre>\n<p>(or call it from cron or any other scheduler)<\/p>\n<p>I have also a <a href=\"http:\/\/sickel.net\/blogg\/?p=1506\">system for presenting the data as a svg-graph<\/a>.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Pushing temperaturedata from an arduino setup into a postgresql database <a href=\"https:\/\/sickel.net\/blogg\/?p=1492\">Continue reading <span class=\"meta-nav\">&rarr;<\/span><\/a><\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"jetpack_post_was_ever_published":false,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":""},"categories":[33,28,27],"tags":[],"class_list":["post-1492","post","type-post","status-publish","format-standard","hentry","category-arduino","category-php","category-sql"],"jetpack_featured_media_url":"","jetpack_shortlink":"https:\/\/wp.me\/pnVtD-o4","jetpack_sharing_enabled":true,"jetpack_likes_enabled":true,"jetpack-related-posts":[],"_links":{"self":[{"href":"https:\/\/sickel.net\/blogg\/index.php?rest_route=\/wp\/v2\/posts\/1492","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/sickel.net\/blogg\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/sickel.net\/blogg\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/sickel.net\/blogg\/index.php?rest_route=\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/sickel.net\/blogg\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=1492"}],"version-history":[{"count":12,"href":"https:\/\/sickel.net\/blogg\/index.php?rest_route=\/wp\/v2\/posts\/1492\/revisions"}],"predecessor-version":[{"id":1511,"href":"https:\/\/sickel.net\/blogg\/index.php?rest_route=\/wp\/v2\/posts\/1492\/revisions\/1511"}],"wp:attachment":[{"href":"https:\/\/sickel.net\/blogg\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=1492"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/sickel.net\/blogg\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=1492"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/sickel.net\/blogg\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=1492"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}