Month: August 2019

I have got nice 3phase 30(100)A four wire RS485 MODBUS Din Rail watt meter from Aliexpress (US $35.76) (max. baud rate is possible to set to 9600). I am able to read values using USB-RS485 adapter + Raspberry pi and sending data (json) to the mqtt server. So far no problem. Power meter is providing cummulated value (the same value you can read on the display).

At the first beginning I was sending the data in to the sqlite database using sqlite node-red node. The table was very simple:
(ID, TIMESTAMP, TOTAL_KWH).

The issue starts with the simple question: How to get daily (hourly, weekly, monthly… ) power consumption? Something like:
| 22.7.2019 | 450kWh|
| 23.7.2019 | 320kWh|
| 24.7.2019 | 300kWh|
At the first sight it looks as an easy task, but I ended up with this quite complex query. And as data was growing, the execution of the SELECT was slowing down.

The guys from the nice JOYSFERA server were pointing me to the influxDB. Installation was an easy part thanks to the:
https://www.osradar.com/install-influxdb-ubuntu-18-04-debian-9/

To import existing data in to the influxDB you need a text file with the following structure:

# DDL
CREATE DATABASE mrazirna

# DML
# CONTEXT-DATABASE: mrazirna
# CONTEXT-RETENTION-POLICY:autogen

power value=0.0 1564391161
power value=270.8 1564583065
power value=272.1 1564583462
power value=272.1 1564583542
power value=272.1 1564583621
power value=272.2 1564583701

While preparing the file, note the line ending has to be linux style and if you are importing big data-set, the error messages are displayed on the top of the import screen – you need to scroll up (which was kind of difficult to me to discover).
Now you are ready to import data in to the database:

> influx -import -path=/path/to/the/data/file -precision=s
2019/08/27 22:53:04 Processed 1 commands
2019/08/27 22:53:04 Processed 26934 inserts
2019/08/27 22:53:04 Failed 0 inserts

Next connect the node-red node to the influx database:
https://diyprojects.io/node-red-tutorial-saving-mysensors-measurements-on-influxdb/#.XWWvovxS9B8

When you are preparing data for the influxDB, remember you need to send the same data-type in to the database. In my case the value datatype was float

> show field keys
name: power
fieldKey fieldType
-------- ---------
value    float

It was necessary to writhe following (note *1.0 at the end of the first row) in to the node-red function node (otherwise the string was send to the influxDB)

msg.payload = msg.payload.POWER.toFixed(1)*1.0;
return msg;

The best part is the simplicity of extracting data:

> root@maxbox:/data/new# influx -precision rfc3339
Connected to http://localhost:8086 version 1.7.7
InfluxDB shell version: 1.7.7

> use mrazirna
Using database mrazirna

> select derivative(max(value)) from "power" where time > now()-7d group by time(1d) fill(0) tz('Europe/Prague')
name: power
time                      kWh
----                      ---
2019-08-21T00:00:00+02:00 200.19999999999982
2019-08-22T00:00:00+02:00 199.5
2019-08-23T00:00:00+02:00 202.10000000000036
2019-08-24T00:00:00+02:00 111.80000000000018
2019-08-25T00:00:00+02:00 309.89999999999964
2019-08-26T00:00:00+02:00 222.5
2019-08-27T00:00:00+02:00 278
2019-08-28T00:00:00+02:00 6.5

So far I have following working code (working with example data)

WITH miniPow as (
	select date(TIMESTAMP,'+1 day') as d, max(TOTAL_KWH) mini
	from power 
	group by date(timestamp)
	)
, maxiPow as (
	select date(TIMESTAMP) as d, max(TOTAL_KWH) maxi
	from power 
	group by date(timestamp)
	)
select maxiPow.d, ROUND(maxi-mini, 1) from miniPow 
 join 
maxiPow
on miniPow.d = maxiPow.d	

Below my first SQL attempt (not really elegant)

WITH consumption AS
  (SELECT date(TIMESTAMP) AS d,
          min(TOTAL_KWH) mi,
          max(TOTAL_KWH) ma
   FROM power
   GROUP BY date(TIMESTAMP)),
     enumerated AS
  (SELECT *,

     (SELECT count(*)
      FROM consumption b
      WHERE a.d >= b.d) AS cnt
   FROM consumption a),
     preenumerated AS
  (SELECT *,

     (SELECT count(*)-1
      FROM consumption b
      WHERE a.d >= b.d) AS cnt
   FROM consumption a)
SELECT strftime('%d.%m.%Y', en.d) AS "období od",
       strftime('%d.%m.%Y', pre.d) AS "období do",
       printf("%.1f", en.ma) AS "počáteční kWh",
       printf("%.1f", pre.ma) AS "koncový kWh",
       printf("%.1f", pre.ma-en.ma) AS "kWh za období",
       (JulianDay(pre.d) - JulianDay(en.d)) AS "počet dnů",
       printf("%.1f", (pre.ma-en.ma)/(JulianDay(pre.d) - JulianDay(en.d))) AS "kWh za den"
FROM enumerated en
JOIN preenumerated pre ON en.cnt = pre.cnt
UNION ALL
SELECT strftime('%d.%m.%Y', min(date(TIMESTAMP))) AS "období od",
       strftime('%d.%m.%Y', max(date(TIMESTAMP))) AS "období do",
       "" AS "počáteční kWh",
       "CELKEM:" AS "koncový kWh",
       printf("%.1f kWh", max(TOTAL_KWH)) AS "kWh za období",
       printf("za %.0f dnů", (JulianDay(max(date(TIMESTAMP))) - JulianDay(min(date(TIMESTAMP))))) AS "počet dnů",
       printf("ø %.1f kWh/den", (max(TOTAL_KWH)-min(TOTAL_KWH))/(JulianDay(max(date(TIMESTAMP))) - JulianDay(min(date(TIMESTAMP))))) AS "kWh za den"
FROM power

Link to example table here