{"id":227,"date":"2019-08-28T00:59:38","date_gmt":"2019-08-27T22:59:38","guid":{"rendered":"https:\/\/petr.maxbox.cz\/?p=227"},"modified":"2019-10-12T03:19:44","modified_gmt":"2019-10-12T01:19:44","slug":"monitoring-power-consumption-kwh-using-node-red-and-influxdb","status":"publish","type":"post","link":"https:\/\/petr.maxbox.cz\/index.php\/2019\/08\/28\/monitoring-power-consumption-kwh-using-node-red-and-influxdb\/","title":{"rendered":"Power consumption (kWh) monitoring using node-red and influxDB"},"content":{"rendered":"\n

I have got nice 3phase 30(100)A four wire RS485 MODBUS Din Rail watt meter from Aliexpress (US $35.76) <\/a>(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).<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

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).<\/p>\n\n\n\n

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<\/a>. And as data was growing, the execution of the SELECT was slowing down.<\/p>\n\n\n\n

The guys from the nice JOYSFERA<\/a> 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\/<\/a><\/p>\n\n\n\n

To import existing data in to the influxDB you need a text file with the following structure:<\/a><\/p>\n\n\n\n

# DDL\nCREATE DATABASE mrazirna\n\n# DML\n# CONTEXT-DATABASE: mrazirna\n# CONTEXT-RETENTION-POLICY:autogen\n\npower value=0.0 1564391161\npower value=270.8 1564583065\npower value=272.1 1564583462\npower value=272.1 1564583542\npower value=272.1 1564583621\npower value=272.2 1564583701<\/pre>\n\n\n\n
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:<\/p>\n\n\n\n
> influx -import -path=\/path\/to\/the\/data\/file -precision=s\n2019\/08\/27 22:53:04 Processed 1 commands\n2019\/08\/27 22:53:04 Processed 26934 inserts\n2019\/08\/27 22:53:04 Failed 0 inserts<\/pre>\n\n\n\n
Next connect the node-red node to the influx database: 
https:\/\/diyprojects.io\/node-red-tutorial-saving-mysensors-measurements-on-influxdb\/#.XWWvovxS9B8<\/p>\n\n\n\n
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<\/p>\n\n\n\n
> show field keys\nname: power\nfieldKey fieldType\n-------- ---------\nvalue    float<\/pre>\n\n\n\n
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)<\/p>\n\n\n\n
msg.payload = msg.payload.POWER.toFixed(1)*1.0;\nreturn msg;<\/pre>\n\n\n\n
The best part is the simplicity of extracting data:<\/p>\n\n\n\n
> root@maxbox:\/data\/new# influx -precision rfc3339\nConnected to http:\/\/localhost:8086 version 1.7.7\nInfluxDB shell version: 1.7.7\n\n> use mrazirna\nUsing database mrazirna\n\n> select derivative(max(value)) from \"power\" where time > now()-7d group by time(1d) fill(0) tz('Europe\/Prague')\nname: power\ntime                      kWh\n----                      ---\n2019-08-21T00:00:00+02:00 200.19999999999982\n2019-08-22T00:00:00+02:00 199.5\n2019-08-23T00:00:00+02:00 202.10000000000036\n2019-08-24T00:00:00+02:00 111.80000000000018\n2019-08-25T00:00:00+02:00 309.89999999999964\n2019-08-26T00:00:00+02:00 222.5\n2019-08-27T00:00:00+02:00 278\n2019-08-28T00:00:00+02:00 6.5\n<\/pre>\n","protected":false},"excerpt":{"rendered":"
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<\/p>\n
Continue Reading… Power consumption (kWh) monitoring using node-red and influxDB<\/span>Continue Reading…<\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1],"tags":[],"class_list":["post-227","post","type-post","status-publish","format-standard","hentry","category-nezarazene"],"_links":{"self":[{"href":"https:\/\/petr.maxbox.cz\/index.php\/wp-json\/wp\/v2\/posts\/227","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/petr.maxbox.cz\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/petr.maxbox.cz\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/petr.maxbox.cz\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/petr.maxbox.cz\/index.php\/wp-json\/wp\/v2\/comments?post=227"}],"version-history":[{"count":12,"href":"https:\/\/petr.maxbox.cz\/index.php\/wp-json\/wp\/v2\/posts\/227\/revisions"}],"predecessor-version":[{"id":283,"href":"https:\/\/petr.maxbox.cz\/index.php\/wp-json\/wp\/v2\/posts\/227\/revisions\/283"}],"wp:attachment":[{"href":"https:\/\/petr.maxbox.cz\/index.php\/wp-json\/wp\/v2\/media?parent=227"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/petr.maxbox.cz\/index.php\/wp-json\/wp\/v2\/categories?post=227"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/petr.maxbox.cz\/index.php\/wp-json\/wp\/v2\/tags?post=227"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}