Archives for October 2015

Capturing ESP8266 Max Value on ThingSpeak

12th October 2015 by InternetOfHomeThings in All Posts, ESP8266 : An IoT System on Chip, Internet of Things | Comments Off on Capturing ESP8266 Max Value on ThingSpeak

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 retrieve the data points and search for the maximum among them. Well, read on…

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.

One of my key data points of interest is the lapse time between ESP8266 resets. The value is saved to the 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.

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…

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

Here’s the details…

System Overview

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.

The ESP8266 runs it’s own firmware serving 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.

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.

It should be noted here that this is not limited to an ESP8266. Any MCU capable of these two functions can be used.

overview

PHP CRON Script

So what exactly is a CRON script?

CRON originated in the Unix world. In this case, it is simply a service provide by the web host to automatically execute a script at a predetermined time. There are a couple of  options available to deploy automated scripts for data exchange to a ThingSpeak channel.

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.

But there is a limitation with this approach…

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  complicate the design. So I have used a different approach.

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. But even this approach comes with restrictions…

In order to prevent excessive resource consumption, my web host limits my account to three scheduled scripts.

The other stipulation is that the scripts 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.

The script tasks are simple and straight-forward…

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

And here is the script.

PHP mySQL/ThingSpeak interface
 
  1. <?php
  2.     include("sensors_weather_save_utils.php");
  3.     $diostatus = "";
  4.     define("MAXONTIME", 420); //7 minutes (60 * 7)
  5.     //----------------------------------------
  6.     //-----Get current Epoc time (to local)
  7.     //----------------------------------------
  8.     $now = time();
  9.     date_default_timezone_set('America/Los_Angeles');
  10.     $localtime_assoc = localtime(time(), true);
  11.     $current_hr = $localtime_assoc['tm_hour'];
  12.     $current_mn = $localtime_assoc['tm_min'];
  13.     //----------------------------------------
  14.     //specific time tasks
  15.     //----------------------------------------
  16.     if( $current_mn==0 ) {
  17. //Save Home Weather Sensors to database on-the-hour
  18.         require 'sensors_weather_save.php'; 
  19.     }
  20.     if( ($current_hr==7) && ($current_mn==20) ) {
  21. //Now lets turn off the front porch light if it is 7:20 am
  22.         require 'iot_dtdoff.php'; 
  23.     }
  24.     //----------------------------------------
  25.     //Run every 20 minutes)
  26.     //----------------------------------------
  27.     //Check max time: Get ms since ESP8266 started
  28.     $temp_tm = getEsp8266Sensor($Esp8266SensorURL,"Y","SYS_Time");
  29.     //Get Thingspeak Channel 45834 field1:Max System Time)
  30.     $ThingsSpeakURL = "https://api.thingspeak.com/channels/"."45834".
  31. "/feed/last.json?api_key=".$thingspeak45834;
  32.     $max_up = getEsp8266Sensor($ThingsSpeakURL,"Y","field1");
  33.     if($temp_tm > $max_up) {
  34.      //Update ThingSpeak field1
  35.         $ThingsSpeakURL = "https://api.thingspeak.com/update?key=".$thingspeak45834.
  36. "&field1=".$temp_tm;
  37.         get_fcontent($ThingsSpeakURL);
  38.     }
  39.     $status = file_get_contents($modtronixURL);
  40.     $status_array = json_decode($status);
  41.     //----------------------------------------
  42.     //-----find the dio port f byte value-----
  43.     //----------------------------------------
  44.     foreach($status_array as $key => $value) {
  45.         if(strstr($key,"pf")) {
  46.          $diostatus .= $value;
  47.         }
  48.     }
  49.     $diostatus = "B".$diostatus; //Binary prefix
  50.     //----------------------------------------
  51.     //-----get the dio port f byte last value
  52.     //----------------------------------------
  53.     // Connect to database (host,username,password,databasename) 
  54. // "@" suppresses errors/warnings
  55.     $link = @mysqli_connect("localhost",$mysqlUser,$mysqlPass,$mysqlUser);
  56.     //check if connection errors
  57.     if(mysqli_connect_error() ) {
  58.      die("Error: Could not connect to database"); //stops php script
  59.     }
  60.     //Selects(takes something out of database) everything(*) from the users table
  61.     $query = "SELECT * FROM myhome";
  62. if($result = mysqli_query($link, $query)) { //returns "TRUE" if successful
  63. $row = mysqli_fetch_array($result);
  64.     }
  65.     //----------------------------------------
  66.     //-----Get database status
  67.     //----------------------------------------
  68.     $laststatus = $row[PortF]; //Get Last Port F Status
  69.     $lasttime = $row[timetag]; //Get Last Epoc time
  70.     //----------------------------------------
  71.     //-----clear dio if no change
  72.     //----------------------------------------
  73.     $lapsesec = $now - $lasttime;
  74.     echo ($lapsesec);
  75.     if($diostatus != "B11111111") {
  76.     if($lapsesec > MAXONTIME) {
  77.             file_get_contents($resetportfURL); //Reset Port F (Sprinklers off)
  78.         }
  79.     }
  80. //----------------------------------------
  81.     //-----update database status
  82.     //----------------------------------------
  83.     $query = "UPDATE `myhome` SET `PortF` = '".$diostatus."' WHERE id=0 LIMIT 1";
  84.     mysqli_query($link, $query);
  85.     $query = "UPDATE `myhome` SET `timetag` = '".$now."' WHERE id=0 LIMIT 1";
  86.     mysqli_query($link, $query);
  87. ?>

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

As you can see, for example, the values are read and saved every hour using the “sensors_weather_save.php” script, while 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.

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.

Conclusion

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.

Taking this the next logical step, I am planning a follow-up post to use the ThingSpeak Timer and ThingHTTP to accomplish the same thing within the ThingSpeak framework. This will, however, require the added complexity of a php proxy in the absence of a ThingSpeak mySQL API.

Stay tuned for that…

Loading

Share This:
FacebooktwitterredditpinterestlinkedintumblrFacebooktwitterredditpinterestlinkedintumblr
Press Ctrl+C to copy the following code.
"