Like most programmers, I’m lazy in some aspects.
One of them is updating databases to the newest versions :)

My InfluxDB for PV Dashboard is still an older 1.7 version. Everything works great and there is no need to upgrade, but a few days ago I need to rebuild a few queries due to billing changes from my power company, Tauron.

What had to be done?

Until now energy consumption and production were balanced in real-time, every second. From the 1st of April, this changed. Now energy is balanced hourly, so your auto consumption will be higher. That’s a good change, but it requires rewriting queriesthat provide measurement data to Grafana Dashboard.

As you expected, my sluggishness and using older InfluxDB brought some problems.

Making operations on two different measurements

From the very beginning, it wasn’t going my way. It turned out that the older version is not supporting operations between two different measurements. Well, that’s a big problem. My eMeter consumption and production were different measurements.

After doing little research I found the easiest and fastest solution. Combine those two measurements into one. You would ask: how you can do it? Clever :)

Two queries that you can find below did the trick and created a new „energy” measurement with two values – production and consumption. Easy, right?

SELECT value AS production INTO energy FROM energy_production GROUP BY *
SELECT value AS consumption INTO energy FROM energy_consumption GROUP BY *

Mathematical operations are not working if…

there is no value in one of them even if you specified fill(0) as the query option. I was hoping for an elegant solution, but haven’t found one.

So it is what it is, ugly „consumption” > „production” in WHERE clause :/ Thanks to that trick last(„production”) – last(„consumption”) is working in below query.

SELECT (last("production") - last("consumption")) * -1
FROM (SELECT last("production") as "production",
             last("consumption") as "consumption" 
      FROM "autogen"."energy" 
      WHERE $timeFilter GROUP BY time(1h) fill(0))
WHERE $timeFilter AND "consumption" > "production"
GROUP BY time(1h) fill(0)

I lost my optimized queries due to hourly balancing

Last but not least, my optimized queries have ended their life. I spent some time tweaking them and making the Grafana dashboard faster by offloading calculations to the database.

Due to hourly balancing grouping can’t be done by 1d and I can’t use functions like sum() anymore. Some logic temporarily migrated from Query to Transform tab in Grafana.

After getting the first invoice from the power company I will revise and verify all queries. Maybe I will find a way to optimize them and restore the old Grafana dashboard speed.

Summary

The above short story confirms that beinglazy in updating resources could lead to problems that you could omit. After hours spent on changing queries I haven’t updated InfluxDB, maybe it should be done? Who knows, maybe I should add it to my bucket list :)

Did you have a similar situation to mine? Older software gave you trouble? Let me know in the comment section!