Smart meter part 2: Data storage and price info

To present the electricity usage in a nice way we first need store the data somewhere and get hold of the price information.

"Usage in itself doesn't paint the whole picture, so the price information is also fetched and stored." 📸: Audun Braastad / NTB Scanpix
"Usage in itself doesn't paint the whole picture, so the price information is also fetched and stored." 📸: Audun Braastad / NTB Scanpix Vis mer

In the previous post I showed how I fetch and decode the power usage from my smart meter.

Before the grand final I'll go through the middle step where I upload the data and make it available for querying.

Also, usage in itself doesn't paint the whole picture, so the price information is also fetched and stored.

Electricity price

Price Structure
In Norway we have to pay for both the electricity usage itself and a network tariff. The latter has two components; a fixed price and a cost per kWh. The price is different from power company to power company.

At the time of writing the price for me was as follows:

  • Tariff (Nettleie)
    - Fixed component (Fastledd): NOK 2,615 per year
    - Energy component (Energiledd): NOK 0.4202 per kWh
  • Electricity price
    NOK 0.4663 per kWh

Most of the time I'm paying some variant of spot price for the electricity. It varies from day to day. The tariff is set yearly with its energy component being a bit cheaper in the summer than in the winter.

Fetching the price info
I didn't find a publicly available free API for the electricity spot price, so I used some power company website's Ajax call to get the updated price.

Using requests, the Python script I have that uploads the usage also checks for the current price with a call to this REST endpoint every hour.

I haven't looked that hard, but I haven't seen any APIs at all for the tariff at all. But being set once a year it doesn't take much time or energy to set it manually.

Storing the data

As I'm pretty familiar with Google's Firebase tech stack I landed on using the NoSQL Cloud Firestore for storing the usage data.

«I landed on using the NoSQL Cloud Firestore.»

To my surprise I would have to recompile Python on my Raspberry Pi to be able to use the firebase-admin client library directly in my code. I didn't want to do that, so I decided to use Cloud Functions for an endpoint that could receive the data from my Raspberry Pi.

I've tweaked the code a bit here, but generally this is what the function does. Remember to add some level of authentication to not leave it wide open.

const functions = require('firebase-functions');
const admin = require('firebase-admin');
admin.initializeApp(functions.config().firebase);

exports.registerReading = functions.https.onRequest((request, response) => {
    try {
        let incomingReadings = request.body;
        let db = admin.firestore();
        let dbReadings = [];
        for (const incomingReading of incomingReadings) {
            if (isValid(incomingReading)) {
                let dbReading = {
                    "meterId": incomingReading["meterId"],
                    "parseTimeUtc": incomingReading["parseTimeUtc"],
                    "dataTimeLocal": incomingReading["dataTimeLocal"],
                    "meterType": incomingReading["meterType"],
                    "activePower+": incomingReading["activePower+"],
                    "activePower-": incomingReading["activePower-"],
                    "l1Voltage": incomingReading["l1Voltage"],
                    "l2Voltage": incomingReading["l2Voltage"],
                    "l3Voltage": incomingReading["l3Voltage"],
                    "price": incomingReading["price"],
                    "priceTimeUtc": incomingReading["priceTimeUtc"],
                    "raw": incomingReading["raw"] // The raw bytes coming from the meter
                }
                var collectionId = incomingReading.meterId; // We use the meter id as the collection ID
                if (incomingReading["activeEnergy+"] || incomingReading["activeEnergy-"]) { // Hourly reading
                    dbReading["activeEnergy+"] = incomingReading["activeEnergy+"];
                    dbReading["activeEnergy-"] = incomingReading["activeEnergy-"];
                    collectionId += '-energy'; // We use a different collection for when having the hourly energy readings
                }
                let docRef = db.collection(collectionId).doc();
                docRef.set(dbReading);
                dbReadings.push(dbReading);
            } else {
                // [...error handling...]
            }
        }
    } catch (e) {
        // [...error handling...]
    }
    // [...]
});

I soon discovered that uploading the usage every 10 second blew the daily free Functions quota of 5,000 function invocations. So I changed to uploading two readings in one go - ending up on ~4,300 invocations a day.

Querying the data

Now that the data is safely stored in Firestore it's ready to be queried and presented in any way desired. For me it was easiest to Cloud Functions for reading the data back out as well.

Here's a very basic quick and dirty example of doing some querying of the data:

const functions = require('firebase-functions');
const admin = require('firebase-admin');

admin.initializeApp(functions.config().firebase);

exports.getReadings = functions.https.onRequest((request, response) => {
    try {
        let db = admin.firestore();
        var meterRef = db.collection('2200567223197714-energy');
        var responseStr = "Today's first reading: \n";
        var queryRef = meterRef.where('dataTimeLocal', '>=', '2019-07-29T00:00:00')
            .where('dataTimeLocal', '<', '2019-07-30T00:00:00')
            .orderBy('dataTimeLocal')
            .limit(1);
        queryRef.get().then(snapshot => {
            if (snapshot.empty) {
                responseStr += 'No matches\n';
            } else {
                snapshot.forEach(doc => {
                    responseStr += JSON.stringify(doc.data(), null, 4) + '\n';
                });
            }
            return true;
        }).then(() => {
            responseStr += "Today's latest reading: \n";
            queryRef = meterRef.where('dataTimeLocal', '>=', '2019-07-29T00:00:00')
                .where('dataTimeLocal', '<', '2019-07-30T00:00:00')
                .orderBy('dataTimeLocal', 'desc')
                .limit(1);
            queryRef.get()
                .then(snapshot => {
                    if (snapshot.empty) {
                        responseStr += 'No matches\n';
                    } else {
                        snapshot.forEach(doc => {
                            responseStr += JSON.stringify(doc.data(), null, 4) + '\n';
                        });
                    }
                    response.send(responseStr);
                    return true;
                })
                .catch(err => {
                    responseStr += 'Error: ' + err + '\n';
                    response.send(responseStr);
                    console.error(err.stack);
                });
            return true;
        }).catch(err => {
            responseStr += 'Error: ' + err + '\n';
            response.send(responseStr);
            console.error(err.stack);
        });
    } catch (e) {
        responseStr += 'Error: ' + err + '\n';
        response.send(responseStr);
        console.error(err.stack);
    }
});

The final part

So now we have reading, decoding, storing and querying the power usage data.

That's all nice and well, but it doesn't give much value in itself.

In the next post I'll show the end product - how I chose to present the data in what I felt was a meaningful and valuable way. For me that is the most interesting and fun part.