{"id":1032,"date":"2015-10-12T15:10:50","date_gmt":"2015-10-12T22:10:50","guid":{"rendered":"http:\/\/internetofhomethings.com\/homethings\/?p=1032"},"modified":"2015-10-12T15:10:50","modified_gmt":"2015-10-12T22:10:50","slug":"capturing-esp8266-max-value-on-thingspeak","status":"publish","type":"post","link":"https:\/\/internetofhomethings.com\/homethings\/?p=1032","title":{"rendered":"Capturing ESP8266 Max Value on ThingSpeak"},"content":{"rendered":"

\"\"<\/a><\/p>\n

This article provides a method to capture and store the maximum value reached in a ThingSpeak channel field.<\/p>\n

So what’s the big deal? You’ve got all the data captured. Just retrieve the data points and search for the maximum among them. Well, read on…<\/p>\n

You see, like many users, my ThingSpeak channel receives data captured by an ESP8266 micro-control unit (MCU). Both ESP8266 System Status and Sensor data are collected.<\/p>\n

One of my\u00a0key data points of interest is the lapse time between ESP8266 resets. The\u00a0value is saved to\u00a0the ThingSpeak channel in a field containing the ESP8266 ms running time. This, along with the sensor data is added to the channel data once each hour.<\/p>\n

What I found was that reading all of the captured data and searching through it for a maximum value became increasingly slower as my database record count grew. It needed to run quicker. So I came up with a simple solution…<\/p>\n

A method of comparing each new ESP8266 reading with the maximum value saved in a ThingSpeak channel field. This requires\u00a0a CRON script running on my web server. The script is executed once every 20 minutes. What? It’s really not that complicated.<\/p>\n

Here’s the details…<\/p>\n

System Overview<\/strong><\/h4>\n

The system is comprised of an ESP8266, a web host server capable of running CRON scripts, and a ThingSpeak channel. The CRON script is the key component used to move the information from the ESP8266 to the ThingSpeak channel.<\/p>\n

The ESP8266 runs it’s own firmware\u00a0serving two primary purposes. Periodically, it refreshes sensor readings and various system parameters, including the MCU run time since the last reset. This process runs on an internally registered timer callback.<\/p>\n

The ESP8266 also acts as a web server. In this capacity, the server responds to http GET requests, returning sensor and system values in JSON format.<\/p>\n

It should be noted here that this is not limited to an ESP8266. Any MCU capable of these two functions can be used.<\/p>\n

\"overview\"<\/a><\/p>\n

PHP CRON Script<\/strong><\/h4>\n

So what exactly is a CRON script?<\/p>\n

CRON originated in the Unix world. In this case, it\u00a0is simply a service provide by the web host to automatically execute a script at a predetermined time. There are\u00a0a couple of \u00a0options available to deploy automated scripts for data exchange to a ThingSpeak channel.<\/p>\n

Organically, ThingSpeak provides the TimerControl App to perform an action at a specific time or on a regular schedule. This, along with the ThingSpeak ThingHTTP App is all that is needed. ThingHTTP interacts with the micro-controller using http GET or POST.<\/p>\n

But there is a limitation with this approach…<\/p>\n

You see, in my case, I also need to interface with a mySQL database. And ThingSpeak does not provide an API for that purpose. At least not yet. Sure, a proxy interface could be developed using a php script, that would unnecessarily\u00a0\u00a0complicate the design. So I have used a different approach.<\/p>\n

For this discussion, the CRON script uses php to interface both with ThingSpeak and a mySQL database. This eliminates the need for a proxy to overcome the ThingSpeak App’s lack of a mySQL interface.\u00a0But even this approach comes with\u00a0restrictions…<\/p>\n

In order to prevent excessive resource consumption, my web host limits my account to three scheduled scripts.<\/p>\n

The other stipulation is that the\u00a0scripts cannot run continuously. The job should execute and complete within a “reasonable” amount of time. For this simple case, less than 10 seconds should be required.<\/p>\n

The script tasks are simple and straight-forward…<\/p>\n

    \n
  1. Read the current ESP8266 Sensor and status values (HTTP GET)<\/li>\n
  2. Write the Sensor and status values to ThingSpeak<\/li>\n
  3. Write all values to the mySQL database<\/li>\n
  4. Read the max run-time value from ThingSpeak<\/li>\n
  5. Evaluate whether current value for run-time is greater than the max value saved<\/li>\n
  6. Write the max value back to ThingSpeak<\/li>\n
  7. Write the other sensor and status values to ThingSpeak<\/li>\n
  8. Write all values to the mySQL database<\/li>\n<\/ol>\n

    And here is the script.<\/p>\n

     <?php\r\n\u00a0\u00a0\u00a0\u00a0include(\"sensors_weather_save_utils.php\");\r\n\r\n\u00a0\u00a0\u00a0\u00a0$diostatus\u00a0=\u00a0\"\";\r\n\r\n\u00a0\u00a0\u00a0\u00a0define(\"MAXONTIME\",\u00a0420);\u00a0\/\/7\u00a0minutes\u00a0(60\u00a0*\u00a07)\r\n\r\n\u00a0\u00a0\u00a0\u00a0\/\/----------------------------------------\r\n\u00a0\u00a0\u00a0\u00a0\/\/-----Get\u00a0current\u00a0Epoc\u00a0time\u00a0(to\u00a0local)\r\n\u00a0\u00a0\u00a0\u00a0\/\/----------------------------------------\r\n\u00a0\u00a0\u00a0\u00a0$now\u00a0=\u00a0time();\r\n\u00a0\u00a0\u00a0\u00a0date_default_timezone_set('America\/Los_Angeles');\r\n\u00a0\u00a0\u00a0\u00a0$localtime_assoc\u00a0=\u00a0localtime(time(),\u00a0true);\r\n\r\n\u00a0\u00a0\u00a0\u00a0$current_hr\u00a0=\u00a0$localtime_assoc['tm_hour'];\r\n\u00a0\u00a0\u00a0\u00a0$current_mn\u00a0=\u00a0$localtime_assoc['tm_min'];\r\n\r\n\u00a0\u00a0\u00a0\u00a0\/\/----------------------------------------\r\n\u00a0\u00a0\u00a0\u00a0\/\/specific\u00a0time\u00a0tasks\r\n\u00a0\u00a0\u00a0\u00a0\/\/----------------------------------------\r\n\u00a0\u00a0\u00a0\u00a0if(\u00a0$current_mn==0\u00a0)\u00a0{\r\n        \/\/Save Home Weather Sensors to database on-the-hour\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0require\u00a0'sensors_weather_save.php';\u00a0\r\n\u00a0\u00a0\u00a0\u00a0}\r\n\u00a0\u00a0\u00a0\u00a0if(\u00a0($current_hr==7)\u00a0&&\u00a0($current_mn==20)\u00a0)\u00a0{\r\n        \/\/Now lets turn off the front porch light if it is 7:20 am\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0require\u00a0'iot_dtdoff.php';\u00a0\r\n\u00a0\u00a0\u00a0\u00a0}\r\n\u00a0\u00a0\u00a0\u00a0\/\/----------------------------------------\r\n\u00a0\u00a0\u00a0\u00a0\/\/Run every\u00a020\u00a0minutes)\r\n\u00a0\u00a0\u00a0\u00a0\/\/----------------------------------------\r\n\u00a0\u00a0\u00a0\u00a0\/\/Check max time: Get\u00a0ms\u00a0since\u00a0ESP8266\u00a0started\r\n\u00a0\u00a0\u00a0\u00a0$temp_tm\u00a0=\u00a0getEsp8266Sensor($Esp8266SensorURL,\"Y\",\"SYS_Time\");\r\n\r\n\u00a0\u00a0\u00a0\u00a0\/\/Get\u00a0Thingspeak\u00a0Channel\u00a045834\u00a0field1:Max\u00a0System\u00a0Time)\r\n\u00a0\u00a0\u00a0\u00a0$ThingsSpeakURL\u00a0=\u00a0\"https:\/\/api.thingspeak.com\/channels\/\".\"45834\".\r\n    \"\/feed\/last.json?api_key=\".$thingspeak45834;\r\n\u00a0\u00a0\u00a0\u00a0$max_up\u00a0=\u00a0getEsp8266Sensor($ThingsSpeakURL,\"Y\",\"field1\");\r\n\u00a0\u00a0\u00a0\u00a0if($temp_tm\u00a0>\u00a0$max_up)\u00a0{\r\n\u00a0\u00a0\u00a0\u00a0    \/\/Update\u00a0ThingSpeak\u00a0field1\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0$ThingsSpeakURL\u00a0=\u00a0\"https:\/\/api.thingspeak.com\/update?key=\".$thingspeak45834.\r\n        \"&field1=\".$temp_tm;\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0get_fcontent($ThingsSpeakURL);\r\n\u00a0\u00a0\u00a0\u00a0}\r\n\r\n\u00a0\u00a0\u00a0\u00a0$status\u00a0=\u00a0file_get_contents($modtronixURL);\r\n\u00a0\u00a0\u00a0\u00a0$status_array\u00a0=\u00a0json_decode($status);\r\n\r\n\u00a0\u00a0\u00a0\u00a0\/\/----------------------------------------\r\n\u00a0\u00a0\u00a0\u00a0\/\/-----find\u00a0the\u00a0dio\u00a0port\u00a0f\u00a0byte\u00a0value-----\r\n\u00a0\u00a0\u00a0\u00a0\/\/----------------------------------------\r\n\u00a0\u00a0\u00a0\u00a0foreach($status_array\u00a0as\u00a0$key\u00a0=>\u00a0$value)\u00a0{\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0if(strstr($key,\"pf\"))\u00a0{\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0    \u00a0$diostatus\u00a0.=\u00a0$value;\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0}\r\n\u00a0\u00a0\u00a0\u00a0}\r\n\u00a0\u00a0\u00a0\u00a0$diostatus\u00a0=\u00a0\"B\".$diostatus;\u00a0\/\/Binary\u00a0prefix\r\n\u00a0\u00a0\u00a0\u00a0\/\/----------------------------------------\r\n\u00a0\u00a0\u00a0\u00a0\/\/-----get\u00a0the\u00a0dio\u00a0port\u00a0f\u00a0byte\u00a0last\u00a0value\r\n\u00a0\u00a0\u00a0\u00a0\/\/----------------------------------------\r\n\u00a0\u00a0\u00a0\u00a0\/\/\u00a0Connect\u00a0to\u00a0database\u00a0(host,username,password,databasename)\u00a0\r\n    \/\/ \"@\"\u00a0suppresses\u00a0errors\/warnings\r\n\u00a0\u00a0\u00a0\u00a0$link\u00a0=\u00a0@mysqli_connect(\"localhost\",$mysqlUser,$mysqlPass,$mysqlUser);\r\n\r\n\u00a0\u00a0\u00a0\u00a0\/\/check\u00a0if\u00a0connection\u00a0errors\r\n\u00a0\u00a0\u00a0\u00a0if(mysqli_connect_error()\u00a0)\u00a0{\r\n\u00a0\u00a0\u00a0\u00a0    die(\"Error:\u00a0Could\u00a0not\u00a0connect\u00a0to\u00a0database\");\u00a0\/\/stops\u00a0php\u00a0script\r\n\u00a0\u00a0\u00a0\u00a0}\r\n\u00a0\u00a0\u00a0 \/\/Selects(takes\u00a0something\u00a0out\u00a0of\u00a0database)\u00a0everything(*)\u00a0from\u00a0the\u00a0users\u00a0table\r\n\u00a0\u00a0\u00a0\u00a0$query\u00a0=\u00a0\"SELECT\u00a0*\u00a0FROM\u00a0myhome\";\r\n    if($result\u00a0=\u00a0mysqli_query($link,\u00a0$query))\u00a0{\u00a0\/\/returns\u00a0\"TRUE\"\u00a0if\u00a0successful\r\n        $row\u00a0=\u00a0mysqli_fetch_array($result);\r\n\u00a0\u00a0\u00a0\u00a0}\r\n\u00a0\u00a0\u00a0\u00a0\/\/----------------------------------------\r\n\u00a0\u00a0\u00a0\u00a0\/\/-----Get\u00a0database\u00a0status\r\n\u00a0\u00a0\u00a0\u00a0\/\/----------------------------------------\r\n\u00a0\u00a0\u00a0\u00a0$laststatus\u00a0=\u00a0$row[PortF];\u00a0\/\/Get\u00a0Last\u00a0Port\u00a0F\u00a0Status\r\n\u00a0\u00a0\u00a0\u00a0$lasttime\u00a0=\u00a0$row[timetag];\u00a0\/\/Get\u00a0Last\u00a0Epoc\u00a0time\r\n\r\n\u00a0\u00a0\u00a0\u00a0\/\/----------------------------------------\r\n\u00a0\u00a0\u00a0\u00a0\/\/-----clear\u00a0dio\u00a0if\u00a0no\u00a0change\r\n\u00a0\u00a0\u00a0\u00a0\/\/----------------------------------------\r\n\u00a0\u00a0\u00a0\u00a0$lapsesec\u00a0=\u00a0$now\u00a0-\u00a0$lasttime;\r\n\u00a0\u00a0\u00a0\u00a0echo\u00a0($lapsesec);\r\n\u00a0\u00a0\u00a0\u00a0if($diostatus\u00a0!=\u00a0\"B11111111\")\u00a0{\r\n\u00a0\u00a0\u00a0     if($lapsesec\u00a0>\u00a0MAXONTIME)\u00a0{\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0file_get_contents($resetportfURL);\u00a0\/\/Reset\u00a0Port\u00a0F\u00a0(Sprinklers\u00a0off)\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0}\r\n\u00a0\u00a0\u00a0\u00a0}\r\n    \/\/----------------------------------------\r\n\u00a0\u00a0\u00a0\u00a0\/\/-----update\u00a0database\u00a0status\r\n\u00a0\u00a0\u00a0\u00a0\/\/----------------------------------------\r\n\u00a0\u00a0\u00a0\u00a0$query\u00a0=\u00a0\"UPDATE\u00a0`myhome`\u00a0SET\u00a0`PortF`\u00a0=\u00a0'\".$diostatus.\"'\u00a0WHERE\u00a0id=0\u00a0LIMIT\u00a01\";\r\n\u00a0\u00a0\u00a0\u00a0mysqli_query($link,\u00a0$query);\r\n\u00a0\u00a0\u00a0\u00a0$query\u00a0=\u00a0\"UPDATE\u00a0`myhome`\u00a0SET\u00a0`timetag`\u00a0=\u00a0'\".$now.\"'\u00a0WHERE\u00a0id=0\u00a0LIMIT\u00a01\";\r\n\u00a0\u00a0\u00a0\u00a0mysqli_query($link,\u00a0$query);\r\n\r\n?><\/pre>\n
    This script runs every 20 minutes. And while the topic of this post is saving a max value, the other tasks performed by my script are also shown. I figured this would arm you with some actually used ideas on what is possible using a scheduled script to interact with your “things”.<\/p>\n
    As you can see, for example, the values are read and saved every hour using the “sensors_weather_save.php” script,\u00a0while at 7:20 am, my dusk to dawn porch light is turned off. I found this necessary, especially on darker or overcast days. Physically, a bright LED is flashed briefly in front of the light sensor to extinguish the light, if it is on-simply by controlling a digital output signal.<\/p>\n
    The script to save values to mySQL is not shown. In lieu of that, the mySQL interaction exposed in the script illustrates the php interface. Here, it is used to turn off my sprinklers, if they remain on for more than 7 minutes. The check is made every 20 minutes. This was necessary as a watchdog in the event the sprinklers, under IoT control, were inadvertently left or stuck on.<\/p>\n
    Conclusion<\/strong><\/h4>\n
    This information provides a method to automatically update the max value of a ThingSpeak channel field. It also illustrates how to interface with a mySQL database using php. This php bridge script can be used to analyse and manipulate ThingSpeak data in any way needed.<\/p>\n
    Taking this the next logical step, I am planning a follow-up post to use the ThingSpeak Timer and ThingHTTP to\u00a0accomplish the same thing within the ThingSpeak framework. This will, however, require the added complexity of a php proxy in the absence of a ThingSpeak\u00a0mySQL API.<\/p>\n
    Stay tuned for that…<\/p>\n","protected":false},"excerpt":{"rendered":"
    This article provides a method to capture and store the maximum value reached in a ThingSpeak channel field. So what’s the big deal? You’ve got all the data captured. Just …<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1,10,19],"tags":[79,76,78,77],"class_list":["post-1032","post","type-post","status-publish","format-standard","hentry","category-alltheposts","category-esp8266","category-internet-of-things","tag-cron-script","tag-max-value","tag-mysql-php-api","tag-useful-thingspeak-tip"],"_links":{"self":[{"href":"https:\/\/internetofhomethings.com\/homethings\/index.php?rest_route=\/wp\/v2\/posts\/1032","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/internetofhomethings.com\/homethings\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/internetofhomethings.com\/homethings\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/internetofhomethings.com\/homethings\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/internetofhomethings.com\/homethings\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=1032"}],"version-history":[{"count":16,"href":"https:\/\/internetofhomethings.com\/homethings\/index.php?rest_route=\/wp\/v2\/posts\/1032\/revisions"}],"predecessor-version":[{"id":1052,"href":"https:\/\/internetofhomethings.com\/homethings\/index.php?rest_route=\/wp\/v2\/posts\/1032\/revisions\/1052"}],"wp:attachment":[{"href":"https:\/\/internetofhomethings.com\/homethings\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=1032"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/internetofhomethings.com\/homethings\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=1032"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/internetofhomethings.com\/homethings\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=1032"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}