mikepsinn / sqlbee Goto Github PK

            _ _
           | | |
  ___  ____| | |__   ___  ___
 / __|/ _  | |  _ \ / _ \/ _ \
 \__ \ (_| | | |_) |  __/  __/
 |___/\__  |_|____/ \___|\___|
         | |
         |_|

sqlbee is a simple application that queries the ecobee API and extracts thermostat and runtime report data into a mySQL database. This is essentially the data from the System Monitor section of ecobee Home IQ.

This project uses the MIT License, which means you can do whatever you want with the code and that I am not liable for anything. Have fun!

• Extracts current thermostat data like temperature, humidity, setpoints, etc (supports multiple thermostats)
• Extracts runtime report data using the ecobee runtimeReport API endpoint\

• Does NOT offer a means of reading the extracted data
• Does NOT analyze or display the data

• An ecobee thermostat
• An ecobee developer account (free)
• A server with PHP (with the cURL extension) and mySQL; any recent verson of both should work fine

1. Clone this project to a folder on your server.
2. Create the sqlbee database and tables on your mySQL database by running the SQL in sqlbee.sql.
3. Copy or rename configuration.example.php to configuration.php.
4. Create an ecobee developer account (https://www.ecobee.com/developers/).
5. Create your own ecobee app (Developer > Create New) and get the API key and set that as the $client_id variable in configuration.php. Use the PIN Authorization method when creating the app.
6. Set the $database_* variables in configuration.php to match your mySQL connection properties.
7. Execute setup.php by running php -f setup.php and follow the instructions.
8. Set up a cron job to run cron.php at your desired interval. Example crontab entry: * * * * * php -f /var/www/sqlbee/cron.php.

• After getting the project running, you might notice that roughly the past 15 minutes of data in runtime_report have missing data. This is because the API reports these rows but the ecobee only transmits it's local data every 15 minutes.