Install\u00a0a quick MySQL server (does not need to be Internet accessible) and a web server \/ programming language of your choice combination (*does* need to be Internet accessible – we are using Apache and PHP).<\/p>\n
Create the database and a table to hold your energy data:<\/p>\n
mysql> describe EnergyMonitors;\r\n+-----------+-------------+------+-----+-------------------+----------------+\r\n| Field | Type | Null | Key | Default | Extra |\r\n+-----------+-------------+------+-----+-------------------+----------------+\r\n| keyID | int(11) | NO | PRI | NULL | auto_increment |\r\n| monitorID | varchar(50) | YES | | NULL | |\r\n| clampID | varchar(50) | YES | | NULL | |\r\n| eventTime | timestamp | NO | | CURRENT_TIMESTAMP | |\r\n| kwatts | double | YES | | NULL | |\r\n| kwhours | double | YES | | NULL | |\r\n+-----------+-------------+------+-----+-------------------+----------------+\r\n6 rows in set (0.00 sec)<\/pre>\nCreate an ID within your database that\u00a0has read\/write permission to this table. I create another ID that has read-only access (pages displaying data use this ID, the page to post data uses the read\/write ID).<\/p>\n
We also track temperature —\u00a0ideally, we’d be able to compare power consumption based on temperature *and* sunlight (we use a lot less power on a cold sunny day than I expect … just don’t know\u00a0how<\/em> much less) … but I’m not there yet. Currently, the weather database only holds temperature:<\/p>\n
mysql> describe weather;\r\n+--------------+-----------+------+-----+-------------------+-----------------------------+\r\n| Field | Type | Null | Key | Default | Extra |\r\n+--------------+-----------+------+-----+-------------------+-----------------------------+\r\n| recordedTime | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |\r\n| temperature | int(11) | YES | | NULL | |\r\n| id | int(11) | NO | PRI | NULL | auto_increment |\r\n+--------------+-----------+------+-----+-------------------+-----------------------------+\r\n3 rows in set (0.01 sec)<\/pre>\nSince we brought our Bloomsky <\/a>online, I use the Bloomsky API to record\u00a0temperature. Prior to that, I was parsing the XML from NWS’s closest reporting station’s – for Cleveland, that is\u00a0\u00a0http:\/\/w1.weather.gov\/xml\/current_obs\/KCLE.xml<\/a> … there’s\u00a0probably one near you. I grabbed both\u00a0observation_time_rfc822 and temp_f in case observations were not updated regularly\u00a0– the observation time was stored as the recordedTime. The temperature recording script is\u00a0in cron as an hourly task.<\/p>\n
You also need a small\u00a0web page where SmartThings can post\u00a0data\u00a0— in PHP, I have<\/p>\n
<?php\r\nif(!$_POST[\"monitorID\"] || !$_POST[\"clampID\"] ){\r\necho \"<html><body>\\n\";\r\necho \"<form action=\\\"\";\r\necho $_SERVER['PHP_SELF'];\r\necho \"\\\" method=\\\"post\\\">\\n\";\r\necho \"Monitor ID: <input type=\\\"text\\\" name=\\\"monitorID\\\"><br>\\n\";\r\necho \"Clamp ID: <input type=\\\"text\\\" name=\\\"clampID\\\"><br>\\n\";\r\necho \"KW Value: <input type=\\\"text\\\" name=\\\"kwatts\\\"><br>\\n\";\r\necho \"KWH Value: <input type=\\\"text\\\" name=\\\"kWHours\\\"><br>\\n\";\r\necho \"<input type=\\\"submit\\\">\\n\";\r\necho \"<\/form>\\n\";\r\necho \"<\/body><\/html>\\n\";\r\n}\r\nelse{\r\n$strMeter = $_POST[\"monitorID\"];\r\n$strClamp = $_POST[\"clampID\"];\r\n$strKWValue = $_POST[\"kwatts\"];\r\n$strKWHValue = $_POST[\"kWHours\"];\r\nprint \"<pre>Meter: $strMeter\\nClamp: $strClamp\\nKW: $strKWValue\\nKWH: $strKWHValue\\n<\/pre>\\n\";\r\n$servername = \"DatabaseHostname\";\r\n$username = \"MySQLUID\";\r\n$password = 'MySQLPassword';\r\n$dbname = \"HomeAutomation\";\r\n$conn = new mysqli($servername, $username, $password, $dbname);\r\nif ($conn->connect_error) {\r\ndie(\"Connection failed: \" . $conn->connect_error);\r\n}\r\nif($strKWHValue && $$strKWValue){\r\n$sql = \"INSERT INTO EnergyMonitors (monitorID, clampID, kwatts, kwhours) VALUES ('$strMeter', '$strClamp', '$strKWValue', '$strKWHValue')\";\r\n}\r\nelseif($strKWHValue){\r\n$sql = \"INSERT INTO EnergyMonitors (monitorID, clampID, kwhours) VALUES ('$strMeter', '$strClamp', '$strKWHValue')\";\r\n}\r\nelse{\r\n$sql = \"INSERT INTO EnergyMonitors (monitorID, clampID, kwatts) VALUES ('$strMeter', '$strClamp', '$strKWValue')\";\r\n}\r\nif ($conn->query($sql) === TRUE) {\r\necho \"New record created successfully\";\r\n}\r\nelse {\r\necho \"Error: \" . $sql . \"<br>\" . $conn->error;\r\n}\r\n\r\n$conn->close();\r\n}\r\n?><\/pre>\nDatabaseHostname, MySQLUID and MySQLPassword need to be yours too. Since posted data is meant to come from a trusted source (a source where I’ve supplied the URL) and in a known good format, these are quick INSERT statements\u00a0*not* the safest.<\/p>\n
Check in your database that you are getting data, then let it run for a few hours. Once you have a little bit of history, you can start viewing your energy usage. Link it into Excel\/Access using MyODBC<\/a>\u00a0for ad-hoc reporting, write your own code to do exactly what you want, use a generic charting package capable of reading MySQL data …<\/p>\n
I am using pChart<\/a> to display data – the\u00a0chart I use most frequently is the\u00a0stacked bar chart for kWH and a line chart for temperature.\u00a0Here is the PHP code which generates this\u00a0PNG:\u00a0energyUsage-StackedBarChart-Flat<\/a>\u00a0– again,\u00a0DatabaseHostname, MySQLUID, and MySQLPassword need to be yours.<\/p>\n
![\"StackedBarChart-KWHAndTemp\"](\"http:\/\/lisa.rushworth.us\/wp-content\/uploads\/2016\/03\/StackedBarChart-KWHAndTemp-1024x538.png\")
<\/a><\/p>\n