As almost everyone else when COVID 19 hit I was stuck at home for 6 weeks and needed a project.
I dont want a standard Smart Meter for electricity I wanted one I could use, obtain all the data from it so I could store it and use it for a future project.
Looking online a good web site I found is Openenergy they have loads of very good resources and a shop so you can buy any bits needed.
I decided to go with pulse counting, every time your electricity meter flashes it is normally equivalent to 1wh so 1000 flashes are 1kWh. See Pulse counting on Openenergy website for loads of great information. This method can also be used to measure water or gas usage if your meter flashes a measurable measurement. Look up information for your meter online and/or physically look at it, as it can also sometimes show the measurement per flash.
I looked at using a opto sensor and a basic circuit connected to the ESP32. But the quicker and easier version is the nice people at Openenergy do a Optical Utility Meter LED Pulse Sensor
This sticks on to the front of your meter and flashes every time a pulse is seen on the meter.
So connecting this to an ESP32 on an interrupt pin we can count the flashes and therefore the energy used.
The basic project will consist of
1. Optical Pulse Sensor
2. ESP32
3. 5V USB PSU
4. Veroboard or similar
Software is written using Arduino IDE which consists of
1. Wifi connection to local network
2. mySQL connector link to mySQL database
3. Interrupt sub routine counting every flash incrementally (to give a watt per minute value)
4. 60 second timer which fires the write watt hour count to the database
So data is recorded every 60 seconds
After running this for a while we can start to do something with the data (see DIY Smart Electricity Smart Meter ESP32 - Part 2 Display the Data)
Arduino code below
// target to program for DOIT ESP32 DEVKIT V1 80MHZ none on COM3 #include <millisDelay.h> #include <MySQL_Connection.h> #include <MySQL_Cursor.h> #include <WiFi.h> const byte interruptPin = 34; // Pin to put Optical counter on volatile int interruptCounter = 0; int numberOfInterrupts = 0; millisDelay Delay; void IRAM_ATTR handleInterrupt() { //interrupt routine portENTER_CRITICAL_ISR(&mux); interruptCounter++; // increment counter by 1 when called portEXIT_CRITICAL_ISR(&mux); } int energy_count; /* Need a table setup in mySQL to write too. CREATE TABLE test_arduino.energy ( num integer primary key auto_increment, energy integer, recorded timestamp ); */ // MySQL server listening address changed to any and worked as expected after changing user to insert user const char* ssid = "WIFI_SSID"; const char* password = "WIFI_PASSWORD"; char user[] = "arduino"; // MySQL user login username char passwordSQL[] = "arduino"; // MySQL user login password char query[128]; IPAddress server_addr(192,168,0,127); // IP of the MySQL server here WiFiServer server(80); void setup() { pinMode(interruptPin, INPUT_PULLUP); //pull up on interrupt pin attachInterrupt(digitalPinToInterrupt(interruptPin), handleInterrupt, FALLING); connectToNetwork(); Delay.start(60000); } void loop() { /* * use millis to call mysqlloop every 60 seconds * and clear number of interupts and energy count after uploading to mysql is sucessful * */ // check if delay has timed out if (Delay.justFinished()) { Delay.repeat(); // start delay again without driftenergy_count = numberOfInterrupts; //pass interrupt count to energy_countmySqlLoop(); // Call mySql write sub routine } } void connectToNetwork() { WiFi.begin(ssid, password); while (WiFi.status() != WL_CONNECTED) { delay(1000); } } void mySqlLoop(){ char INSERT_SQL[] = "INSERT INTO test_arduino.energy (energy) VALUES (%d)"; WiFiClient client; MySQL_Connection conn((Client *)&client); if (conn.connect(server_addr, 3306, user, passwordSQL)) { // Can check MYSQL connection here } else{ //MYSQL connection failed } // Initiate the query class instance MySQL_Cursor *cur_mem = new MySQL_Cursor(&conn); sprintf(query, INSERT_SQL, energy_count); // Execute the query cur_mem->execute(query); // Note: since there are no results, we do not need to read any data // Deleting the cursor also frees up memory used delete cur_mem; energy_count=0; numberOfInterrupts=0; }
Further description of the code
I always put in a note to remind me of what target it is written for i.e. ESP32, as when you swap between projects on Arduino IDE it remembers what you did last. So If I have just used an Arduino Uno project for example the below would not compile. This is a good safe guard against strange errors.
millisdelay include is for the time delay tracking.
MySQL_connection and MySQL_cursor enable you to use the MySQL writes to the MySQL database.
wifi is used so ESP32 can connect to wifi network.
// target to program for DOIT ESP32 DEVKIT V1 80MHZ none on COM3
#include <millisDelay.h>
#include <MySQL_Connection.h>
#include <MySQL_Cursor.h>
#include <WiFi.h>
setup pin that optical counter will be connected to on the ESP32
Setup an interrupt counter variable
Initialise number of interrupts to 0
millis delay is intialised
const byte interruptPin = 34; // Pin to put Optical counter on
volatile int interruptCounter = 0;
int numberOfInterrupts = 0;
millisDelay Delay;
Iram_attr sets up the as a hardware interrupt pin
Initialise energy_count variable
Create table is the table example that must be setup in mySQL so that you have something to write to.
void IRAM_ATTR handleInterrupt() {
portENTER_CRITICAL_ISR(&mux);
interruptCounter++;
portEXIT_CRITICAL_ISR(&mux);
}
int energy_count;
/* Need a table setup in mySQL to write too.
CREATE TABLE test_arduino.energy (
num integer primary key auto_increment,
energy integer,
recorded timestamp
);
*/
Had a problem with writing to the mySQL database so changed the mySQL server listening address to any on the local network and user had to be changed to an insert user. So that the mySQL Connector can correctly write to the database.
ssid and password constants are for the wifi ssid and wifi password
char user / password SQL are the users details for the mySQL database.
char query is a holder array for the mySQL query
ip address server is the address for the mySQL server
wifiserver sets up the wifi of the ESP32
// MySQL server listening address changed to any and worked as expected after changing user to insert user
const char* ssid = "WIFI_SSID";
const char* password = "WIFI_PASSWORD";
char user[] = "mySQL_user"; // MySQL user login username
char passwordSQL[] = "mySQL_password"; // MySQL user login password
char query[128];
IPAddress server_addr(192,168,0,127); // IP of the MySQL server here
WiFiServer server(80);
Setup has pin mode as an hardware interrupt pin with a pull up resistor on the input
Add the interrupt to the interrupt pin and active on a falling edge.
call connect to network subroutine which is below
Start the millis delay so it completes every minute (or 60 seconds)
void setup() {
pinMode(interruptPin, INPUT_PULLUP); //pull up on interrupt pin
attachInterrupt(digitalPinToInterrupt(interruptPin), handleInterrupt, FALLING);
connectToNetwork();
Delay.start(60000);
}
The main loop of the program as the comment say every 60 seconds calls the mySQL loop so the energy count which is the interrupy count over that time can be added to the mySQL database. Then clearing the interrupt count to start again.
delay finishes then restart the delay
make energy count = to the interrupt count
call the mySQLloop subroutine to write the data to mySQL
void loop() {
/*
* use millis to call mysqlloop every 60 seconds
* and clear number of interupts and energy count after uploading to mysql is sucessful
*
*/
// check if delay has timed out
if (Delay.justFinished()) {
Delay.repeat(); // start delay again without drift
energy_count = numberOfInterrupts; //pass interrupt count to energy_count
mySqlLoop(); // Call mySql write sub routine
}
}
Connect to network subroutine
wifi begin using network ssid and network password
connect to wifi delay for 1 second
void connectToNetwork() {
WiFi.begin(ssid, password);
while (WiFi.status() != WL_CONNECTED) {
delay(1000);
}
}
mySQLloop subroutine
insert_sql is the mySQL statement we want to use to insert the energy value into the database
wifi client which is used by the mySQL connection client
connect to the server address on the mySQL port of 3306 using the mySQL user details we used above
the check MYSQL connection here and the MYSQL connection failed are for fault finding and first setup. Origionally I used these for serial write outs so I could see how the connection was working or not in the case of early versions.
MySQL cursor sets up the where the connection in the database needs to be then we join the insert sql to the energy_count and then it is executed.
The next lines clear the cursor from memory and 0s the energy count and the interrupt counts.
void mySqlLoop(){
char INSERT_SQL[] = "INSERT INTO test_arduino.energy (energy) VALUES (%d)";
WiFiClient client;
MySQL_Connection conn((Client *)&client);
if (conn.connect(server_addr, 3306, user, passwordSQL)) {
// Can check MYSQL connection here
}
else{
//MYSQL connection failed
}
// Initiate the query class instance
MySQL_Cursor *cur_mem = new MySQL_Cursor(&conn);
sprintf(query, INSERT_SQL, energy_count);
// Execute the query
cur_mem->execute(query);
// Note: since there are no results, we do not need to read any data
// Deleting the cursor also frees up memory used
delete cur_mem;
energy_count=0;
numberOfInterrupts=0;
}
I hope the above might help some one trying to do a similar project.
One Problem I did have at the end of this was the ESP32 locks up every 30 or 50 days! A power reset solved this till the next time it happened, I tried doing a watchdog on it and getting it to reset if it locked up, but it did not seem to work. So I went back to basics and put the ESP32 on a timer plug so the 5V supply gets disconected for a couple of minutes every night at midnight. It has been all good since. But would be good to have a nicer way of doing this in the future.
20th August 2022 Have since embraced the future and now have a smart meter with Octopus Energy. Even though I can get all my data online using their API for smart meter data, this software and sensor are still on the front of my smart meter and giving me local data.
Comments
Post a Comment