Totalizer difference example

[bkenobi]

I have a couple sensors that return a running sum of usage (power meter, rain gauge) and I need to split it out for calculating period based usage. This is a common question I've seen posted in various places, but the code that is generally posted uses global variables to hold the period start value. I am new to NR but it appears that the Totalizer function should do what I want in a nice modular package. However, I haven't been able to locate any examples of how to set a flow up using the node. I found one example from 7/2020 that looks like it would work but they only provided a picture rather than a flow export, so I'm not sure how to get started.

Rain volume using totaliser node

Hi, I am a newbie. I m not sure if this is the correct way to use totaliser node for rain volume last 1 hour and 24 hours but the main issue here is that the rain sensor has a stored value of previous rain amount and totaliser keeps adding it up. How do I reset the values and start with the latest inputs? [image]

I'd really appreciate some guidance so I can start learning how to set up such a flow in a smarter way.

[Trying_to_learn]

I don't have the node.

But reading the docs, you set a time period and from now to when the time period expires, it totalises the values.

At the end of the period, it spits out a total.

Doesn't seem that hard.

[Paul-Reed]

@bkenobi

but the code that is generally posted uses global variables to hold the period start value

The advantage of using file based storage when doing these calculations is that the data will survive a full deploy, flows restart, or even a system reboot.
Using the Totalizer node, your data will reset to zero if you do any of the above, which is not ideal.
If you read the link above, and consider some basic maths, to total the difference between the current value and the previous value, you will get a better grasp of how to progress.

If you struggle, please post what you have done, and I'm sure you will get some help.

[bkenobi]

That's a valid point that I wasn't aware of. I assumed that the totalizer worked the same as using global variables but packaged nicely for easier consumption. Since it sounds like that's not the case, I will go back to focusing on the other, more common approach.

I found a flow that was provided including a video guide.
https://flows.nodered.org/flow/6e8e14789d0c4a80e41b5f37a54b663d

I have been looking at the code and trying to figure out what needs to change for my needs. I guess I was hoping that there might be a simpler way, but then again that flow has a lot more than I need and can likely strip it cleaner. I will focus on that first and see how it goes.

Well, unless someone has a link to a dead simple meter flow without all the bells and whistles.

[bkenobi]

I went back and spent more time reading how the flow worked and realized it was only complicated looking because there were so many nodes to build the charts. Since I'm using Grafana, I simply deleted them. In case anyone is looking to utilize, simply use the link above and delete the graph sections. I can post what I did, but it seems wrong since that would be taking some kind of credit for modifying someone else's work.

[Colin]

Are you storing the data in Influxdb? If so then you should be able to get what you want direct from the database.

[bkenobi]

Yes, I'm using Influxdb. The data comes in from these sensors as a running total so I need to do 2 things before it's usable.

1. Find the difference between readings so I can have usable usage data.
2. The meter resets when it reaches it's maximum total value so I need to make the gap take that into account when the new value rolls over to 0.

In addition, I have a desire to implement retention policies such that the fine data is stored for a short time and the coarse data is retained for longer/indefinitely.

I have the code that I thought would accomplish 1 running, but checking this morning shows that there is no data going into the influxdb and there appears to be at least 1 error in the debugger. If there was a simpler way to do this within Influx that would be better. IMO, this is the type of thing databases should be able to do already but I couldn't figure out how.

[Colin]

What exactly are you putting into the database?

[bkenobi]

The weather station generates data that comes into NR from rtl_433 via MQTT. The rain data comes in as a running total of inches of precipitation. I put that directly into the DB. The same message that gets put in the DB is also sent to this new code that calculates the period total which should also save that value to the DB.

I can change the approach if there is a better way to do it, but what my intent was is to save the raw data (running total of precipitation) for now but eventually only keep the period accumulations. The other option that would be easily accomplished would be to record the instantaneous accumulation. That would simply require storing the last reading and time and finding the difference and dividing by time delta. I'm at a point where nothing is final so any suggestions are very much appreciated.

This is the code I'm using right now. It's based on the link above so it's not my creation (not taking credit for it).

[{"id":"319cd392.9843cc","type":"function","z":"c473a0c1.c75468","name":"kWh metering","func":"var global_name = \"kWhstat\";   // Name of the global variable\nvar hourlysize = 24;            // Number of hourly stat to collect (24 for the last 24 hours)\nvar dailysize = 10;             // Number of daily stat to collect\nvar weeklysize = 10;             // Number of weekly stat to collect\nvar current = new Date();\nvar i = 0;\nvar delta = 0;\n\nvar kWhstat = global.get(global_name);\nif (kWhstat===undefined) {\n    kWhstat = {};\n}\n\n// If the message is coming from sensor, save the current value\nif (msg.topic===\"reading\") {\n    if (msg.payload!==undefined) {\n        context.set(\"kWh\",msg.payload);\n        msg.topic=\"reading\";\n    }\n}\n\n// Do not process stats until we have a valid reading\nif (context.get(\"kWh\")!==undefined) {\n\n    // Update the hourly stats\n    if (msg.topic===\"hourly\") {\n        msg.topic=\"hourly\";\n        // First update the historical readings\n        if (kWhstat.hourlyvalues===undefined) {\n            // this is the first reading, create the array first\n            kWhstat.hourlyvalues = [];\n            kWhstat.hourlyvalues.push({epoch: current.getTime(), reading:context.get(\"kWh\")});\n        } else {\n            if (kWhstat.hourlyvalues.length<hourlysize-1) {\n                // there is not enough history collected yet, add to the array\n                kWhstat.hourlyvalues.push({epoch: current.getTime(), reading:context.get(\"kWh\")});\n            } else {\n                // the array is full need to shift values\n                for (i=1;i<kWhstat.hourlyvalues.length;i++) {\n                    kWhstat.hourlyvalues[i-1]=kWhstat.hourlyvalues[i];\n                }\n                // add the current value to the end of the array\n                kWhstat.hourlyvalues[hourlysize-1]={epoch: current.getTime(), reading:context.get(\"kWh\")};\n            }\n        }\n        \n        // Calculate the delta values\n        kWhstat.hourlydelta = [];\n        if (kWhstat.hourlyvalues.length>1) {\n            // We need at least two readings\n            for (i=1;i<kWhstat.hourlyvalues.length;i++) {\n                delta = Math.round((kWhstat.hourlyvalues[i].reading - kWhstat.hourlyvalues[i-1].reading)*10)/10;\n                if (delta<0) {\n                    // If for any reason readings from the sensor reset use delta as zero instead of a high negative value\n                    delta = 0;\n                }\n                kWhstat.hourlydelta.push({epoch: kWhstat.hourlyvalues[i].epoch, delta:delta});\n            }\n        }\n        \n    }\n   \n    // Update the daily stats\n    if (msg.topic===\"daily\") {\n        msg.topic=\"daily\";\n        // First update the historical readings\n        if (kWhstat.dailyvalues===undefined) {\n            // this is the first reading, create the array first\n            kWhstat.dailyvalues = [];\n            kWhstat.dailyvalues.push({epoch: current.getTime(), reading:context.get(\"kWh\")});\n        } else {\n            if (kWhstat.dailyvalues.length<dailysize-1) {\n                // there is not enough history collected yet, add to the array\n                kWhstat.dailyvalues.push({epoch: current.getTime(), reading:context.get(\"kWh\")});\n            } else {\n                // the array is full need to shift values\n                for (i=1;i<kWhstat.dailyvalues.length;i++) {\n                    kWhstat.dailyvalues[i-1]=kWhstat.dailyvalues[i];\n                }\n                // add the current value to the end of the array\n                kWhstat.dailyvalues[dailysize-1]={epoch: current.getTime(), reading:context.get(\"kWh\")};\n            }\n        }\n        \n        // Calculate the delta values\n        kWhstat.dailydelta = [];\n        if (kWhstat.dailyvalues.length>1) {\n            // We need at least two readings\n            for (i=1;i<kWhstat.dailyvalues.length;i++) {\n                delta = Math.round((kWhstat.dailyvalues[i].reading - kWhstat.dailyvalues[i-1].reading)*10)/10;\n                if (delta<0) {\n                    // If for any reason readings from the sensor reset use delta as zero instead of a high negative value\n                    delta = 0;\n                }\n                kWhstat.dailydelta.push({epoch: kWhstat.dailyvalues[i].epoch, delta:delta});\n            }\n        }\n        \n    }\n    \n    // Update the weekly stats\n    if (msg.topic===\"weekly\") {\n        msg.topic=\"weekly\";\n        // First update the historical readings\n        if (kWhstat.weeklyvalues===undefined) {\n            // this is the first reading, create the array first\n            kWhstat.weeklyvalues = [];\n            kWhstat.weeklyvalues.push({epoch: current.getTime(), reading:context.get(\"kWh\")});\n        } else {\n            if (kWhstat.weeklyvalues.length<weeklysize-1) {\n                // there is not enough history collected yet, add to the array\n                kWhstat.weeklyvalues.push({epoch: current.getTime(), reading:context.get(\"kWh\")});\n            } else {\n                // the array is full need to shift values\n                for (i=1;i<kWhstat.weeklyvalues.length;i++) {\n                    kWhstat.weeklyvalues[i-1]=kWhstat.weeklyvalues[i];\n                }\n                // add the current value to the end of the array\n                kWhstat.weeklyvalues[weeklysize-1]={epoch: current.getTime(), reading:context.get(\"kWh\")};\n            }\n        }\n        \n        // Calculate the delta values\n        kWhstat.weeklydelta = [];\n        if (kWhstat.weeklyvalues.length>1) {\n            // We need at least two readings\n            for (i=1;i<kWhstat.weeklyvalues.length;i++) {\n                delta = Math.round((kWhstat.weeklyvalues[i].reading - kWhstat.weeklyvalues[i-1].reading)*10)/10;\n                if (delta<0) {\n                    // If for any reason readings from the sensor reset use delta as zero instead of a high negative value\n                    delta = 0;\n                }\n                kWhstat.weeklydelta.push({epoch: kWhstat.weeklyvalues[i].epoch, delta:delta});\n            }\n        }\n        \n    }\n\n\n    // This week calculation\n    if (kWhstat.weeklyvalues!==undefined) {\n        if (kWhstat.weeklyvalues.length>0) {\n            kWhstat.thisweek = Math.round((context.get(\"kWh\") - kWhstat.weeklyvalues[kWhstat.weeklyvalues.length-1].reading)*10)/10;\n            if (kWhstat.thisweek<0) {\n                kWhstat.thisweek = 0.0;\n            }\n        } else {\n            kWhstat.thisweek = 0.0;\n        }\n    }\n    // Today calculation\n    if (kWhstat.dailyvalues!==undefined) {\n        if (kWhstat.dailyvalues.length>0) {\n            kWhstat.today = Math.round((context.get(\"kWh\") - kWhstat.dailyvalues[kWhstat.dailyvalues.length-1].reading)*10)/10;\n            if (kWhstat.today<0) {\n                kWhstat.today = 0.0;\n            }\n        } else {\n            kWhstat.today = 0.0;\n        }\n    }\n    // This hour calculation\n    if (kWhstat.hourlyvalues!==undefined) {\n        if (kWhstat.hourlyvalues.length>0) {\n            kWhstat.thishour = Math.round((context.get(\"kWh\") - kWhstat.hourlyvalues[kWhstat.hourlyvalues.length-1].reading)*10)/10;\n            if (kWhstat.thishour<0) {\n                kWhstat.thishour = 0.0;\n            }\n        } else {\n            kWhstat.thishour = 0.0;\n        }\n    }        \n    \n    // Store the updates\n    global.set(global_name,kWhstat);\n    msg.payload = kWhstat;\n    node.status({fill:\"blue\",shape:\"ring\",text:msg.topic+\" performed\"});\n    return msg;\n} else {\n    node.status({fill:\"red\",shape:\"ring\",text:\"No reading\"});\n}","outputs":1,"noerr":0,"initialize":"","finalize":"","x":400,"y":560,"wires":[["667e06d3.19dc58","1371047c.8c00c4"]]},{"id":"1fa8bf73.a25af9","type":"inject","z":"c473a0c1.c75468","name":"","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"3600","crontab":"","once":false,"onceDelay":"","topic":"hourly","payload":"","payloadType":"date","x":150,"y":620,"wires":[["319cd392.9843cc"]]},{"id":"667e06d3.19dc58","type":"debug","z":"c473a0c1.c75468","name":"","active":true,"tosidebar":true,"console":false,"complete":"false","statusVal":"","statusType":"auto","x":630,"y":560,"wires":[]},{"id":"9119e00.74dcc2","type":"inject","z":"c473a0c1.c75468","d":true,"name":"","repeat":"","crontab":"","once":false,"topic":"","payload":"","payloadType":"date","x":960,"y":1140,"wires":[["2fb6dcc6.6c3254"]]},{"id":"2fb6dcc6.6c3254","type":"function","z":"c473a0c1.c75468","d":true,"name":"Delete history","func":"var rainstat = {};\nglobal.set(\"rainstat\",rainstat);\n","outputs":1,"noerr":0,"x":1160,"y":1140,"wires":[[]]},{"id":"23b110e2.b69ac","type":"inject","z":"c473a0c1.c75468","name":"","repeat":"","crontab":"01 00 * * *","once":false,"onceDelay":"","topic":"daily","payload":"","payloadType":"date","x":150,"y":660,"wires":[["319cd392.9843cc"]]},{"id":"1371047c.8c00c4","type":"switch","z":"c473a0c1.c75468","name":"","property":"topic","propertyType":"msg","rules":[{"t":"eq","v":"reading","vt":"str"},{"t":"eq","v":"hourly","vt":"str"},{"t":"eq","v":"daily","vt":"str"},{"t":"eq","v":"weekly","vt":"str"}],"checkall":"true","repair":false,"outputs":4,"x":630,"y":620,"wires":[[],["3f529c79.3d369c"],["6aa4718d.86dc2"],["6925f6dd.5160a"]]},{"id":"3f529c79.3d369c","type":"function","z":"c473a0c1.c75468","name":"Hourly SQL Update","func":"// This logic prevents multiple zero (delta) readings being stored in the database\n// if the (delta) reading is zero and the previous was zero as well, it will not be stored\nvar delta = msg.payload.hourlydelta[msg.payload.hourlydelta.length-1].delta;\nvar store = true;\nif (delta===0) {\n    if (context.get(\"last\")!==undefined) {\n        if (context.get(\"last\")===0) {\n            store = false;\n        }\n    }\n} \n\nif (store) {\n    context.set(\"last\", delta);\n    msg.topic=\"INSERT OR REPLACE INTO sensor_aggr (epoch,device,sensor,value) VALUES(\"+msg.payload.hourlydelta[msg.payload.hourlydelta.length-1].epoch+\",'maplin','rain_hourly',\"+delta+\");\";\n    return msg;\n}","outputs":1,"noerr":0,"initialize":"","finalize":"","x":890,"y":620,"wires":[["1df0d1ef.f15bfe","6b44aa1e.d0e494"]]},{"id":"6aa4718d.86dc2","type":"function","z":"c473a0c1.c75468","name":"Daily SQL Update","func":"msg.topic=\"INSERT OR REPLACE INTO sensor_aggr (epoch,device,sensor,value) VALUES(\"+msg.payload.dailydelta[msg.payload.dailydelta.length-1].epoch+\",'maplin','rain_daily',\"+msg.payload.dailydelta[msg.payload.dailydelta.length-1].delta+\");\";\n\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","x":890,"y":740,"wires":[["d8329cfc.3b17a","4d140d91.b52154"]]},{"id":"6925f6dd.5160a","type":"function","z":"c473a0c1.c75468","name":"Weekly SQL Update","func":"msg.topic=\"INSERT OR REPLACE INTO sensor_aggr (epoch,device,sensor,value) VALUES(\"+msg.payload.weeklydelta[msg.payload.weeklydelta.length-1].epoch+\",'maplin','rain_weekly',\"+msg.payload.weeklydelta[msg.payload.weeklydelta.length-1].delta+\");\";\n\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","x":900,"y":860,"wires":[["90416865.1b61c","7facd5a5.b37e9c"]]},{"id":"c7fb44e7.0e7d1","type":"inject","z":"c473a0c1.c75468","name":"","repeat":"","crontab":"02 00 * * 1","once":false,"onceDelay":"","topic":"weekly","payload":"","payloadType":"date","x":150,"y":700,"wires":[["319cd392.9843cc"]]},{"id":"f308ef97.4ed3b8","type":"inject","z":"c473a0c1.c75468","name":"","props":[{"p":"payload","v":"rainstat","vt":"global"},{"p":"topic","v":"","vt":"string"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"rainstat","payloadType":"global","x":950,"y":1200,"wires":[["afbc5e3b.7c404"]]},{"id":"afbc5e3b.7c404","type":"debug","z":"c473a0c1.c75468","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","x":1150,"y":1200,"wires":[]},{"id":"8fd376b3.8b048","type":"link in","z":"c473a0c1.c75468","name":"kWhAccumulated rtl_433","links":["28caa636.ad69b2"],"x":75,"y":560,"wires":[["5cbe4259.7abf1c"]]},{"id":"c5859912.8d4b88","type":"debug","z":"c473a0c1.c75468","name":"","active":false,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","statusVal":"","statusType":"auto","x":390,"y":520,"wires":[]},{"id":"5cbe4259.7abf1c","type":"change","z":"c473a0c1.c75468","name":"","rules":[{"t":"set","p":"topic","pt":"msg","to":"reading","tot":"str"}],"action":"","property":"","from":"","to":"","reg":false,"x":190,"y":560,"wires":[["c5859912.8d4b88","319cd392.9843cc"]]},{"id":"1df0d1ef.f15bfe","type":"influxdb out","z":"c473a0c1.c75468","influxdb":"93285a9.26cb8a8","name":"kWh Hourly","measurement":"rtl_433/Blueline_PowerCost_Monitor/kWh_hourly","precision":"","retentionPolicy":"","database":"database","precisionV18FluxV20":"ms","retentionPolicyV18Flux":"","org":"organisation","bucket":"bucket","x":1110,"y":620,"wires":[]},{"id":"d8329cfc.3b17a","type":"influxdb out","z":"c473a0c1.c75468","influxdb":"93285a9.26cb8a8","name":"kWh Daily","measurement":"rtl_433/Blueline_PowerCost_Monitor/kWh_daily","precision":"","retentionPolicy":"","database":"database","precisionV18FluxV20":"ms","retentionPolicyV18Flux":"","org":"organisation","bucket":"bucket","x":1100,"y":740,"wires":[]},{"id":"90416865.1b61c","type":"influxdb out","z":"c473a0c1.c75468","influxdb":"93285a9.26cb8a8","name":"kWh Weekly","measurement":"rtl_433/Blueline_PowerCost_Monitor/kWh_weekly","precision":"","retentionPolicy":"","database":"database","precisionV18FluxV20":"ms","retentionPolicyV18Flux":"","org":"organisation","bucket":"bucket","x":1110,"y":860,"wires":[]},{"id":"6b44aa1e.d0e494","type":"function","z":"c473a0c1.c75468","name":"PUD cost calculator","func":"//https://www.snopud.com/Site/Content/Documents/rates/electricrates_010121.pdf\n/*\neffective date      $/kWh\n1 Oct 2017          0.10414\n1 Apr 2021          0.10118\n1 Apr 2022          0.09822\n1 Apr 2023          0.09527\n1 Apr 2024          0.09231\n1 Apr 2025          0.08935\n*/\n\n/*\nvar now = new Date(\"1/15/2021\");\nvar rate = -1;\n\n//var now = Date().getTime(); // 1501653935994\n//var from = new Date(\"02/08/2017\").getTime(); // gives 1486492200000\n//var to = new Date(\"05/08/2017\").getTime();\n\nif(now <= Date(\"1/4/2021\")) {\n   rate = 0.10414;\n}\n\nelse if(now <= Date(\"1/4/2022\")) {\n   rate = 0.10118;\n}\nelse if(now <= Date(\"1/4/2023\")) {\n   rate = 0.09822;\n}\nelse if(now <= Date(\"1/4/2024\")) {\n   rate = 0.09527;\n}\nelse if(now <= Date(\"1/4/2025\")) {\n   rate = 0.08935;\n}\n\n//msg.payload = msg.payload * rate;\nmsg.payload = rate;\nreturn msg;\n*/\n\nconst now = new Date().getTime();\nnode.warn(now)\nvar rate = -1;\n\nif(now <= new Date(\"2021-4-1\").getTime()) {\n  rate = 0.10414;\n}\nelse if(now <= new Date(\"2022-4-1\").getTime()) {\n  rate = 0.10118;\n}\nelse if(now <= new Date(\"2023-4-1\").getTime()) {\n  rate = 0.09822;\n}\nelse if(now <= new Date(\"2024-4-1\").getTime()) {\n  rate = 0.09527;\n}\nelse if(now <= new Date(\"2025-4-1\").getTime()) {\n  rate = 0.08935;\n}\n\nmsg.payload = msg.payload * rate;\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","x":1130,"y":660,"wires":[["63081499.452104","9f6a9114.8fc648"]]},{"id":"9f6a9114.8fc648","type":"influxdb out","z":"c473a0c1.c75468","influxdb":"93285a9.26cb8a8","name":"AccumulatedCost_hourly","measurement":"rtl_433/Blueline_PowerCost_Monitor/AccumulatedCost_hourly","precision":"","retentionPolicy":"","database":"database","precisionV18FluxV20":"ms","retentionPolicyV18Flux":"","org":"organisation","bucket":"bucket","x":1370,"y":660,"wires":[]},{"id":"63081499.452104","type":"debug","z":"c473a0c1.c75468","name":"","active":false,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","statusVal":"","statusType":"auto","x":1330,"y":620,"wires":[]},{"id":"4d140d91.b52154","type":"function","z":"c473a0c1.c75468","name":"PUD cost calculator","func":"//https://www.snopud.com/Site/Content/Documents/rates/electricrates_010121.pdf\n/*\neffective date      $/kWh\n1 Oct 2017          0.10414\n1 Apr 2021          0.10118\n1 Apr 2022          0.09822\n1 Apr 2023          0.09527\n1 Apr 2024          0.09231\n1 Apr 2025          0.08935\n*/\n\n/*\nvar now = new Date(\"1/15/2021\");\nvar rate = -1;\n\n//var now = Date().getTime(); // 1501653935994\n//var from = new Date(\"02/08/2017\").getTime(); // gives 1486492200000\n//var to = new Date(\"05/08/2017\").getTime();\n\nif(now <= Date(\"1/4/2021\")) {\n   rate = 0.10414;\n}\n\nelse if(now <= Date(\"1/4/2022\")) {\n   rate = 0.10118;\n}\nelse if(now <= Date(\"1/4/2023\")) {\n   rate = 0.09822;\n}\nelse if(now <= Date(\"1/4/2024\")) {\n   rate = 0.09527;\n}\nelse if(now <= Date(\"1/4/2025\")) {\n   rate = 0.08935;\n}\n\n//msg.payload = msg.payload * rate;\nmsg.payload = rate;\nreturn msg;\n*/\n\nconst now = new Date().getTime();\nnode.warn(now)\nvar rate = -1;\n\nif(now <= new Date(\"2021-4-1\").getTime()) {\n  rate = 0.10414;\n}\nelse if(now <= new Date(\"2022-4-1\").getTime()) {\n  rate = 0.10118;\n}\nelse if(now <= new Date(\"2023-4-1\").getTime()) {\n  rate = 0.09822;\n}\nelse if(now <= new Date(\"2024-4-1\").getTime()) {\n  rate = 0.09527;\n}\nelse if(now <= new Date(\"2025-4-1\").getTime()) {\n  rate = 0.08935;\n}\n\nmsg.payload = msg.payload * rate;\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","x":1130,"y":780,"wires":[["2f65d0c1.67598","35918af8.7b724e"]]},{"id":"35918af8.7b724e","type":"influxdb out","z":"c473a0c1.c75468","influxdb":"93285a9.26cb8a8","name":"AccumulatedCost_daily","measurement":"rtl_433/Blueline_PowerCost_Monitor/AccumulatedCost_daily","precision":"","retentionPolicy":"","database":"database","precisionV18FluxV20":"ms","retentionPolicyV18Flux":"","org":"organisation","bucket":"bucket","x":1370,"y":780,"wires":[]},{"id":"2f65d0c1.67598","type":"debug","z":"c473a0c1.c75468","name":"","active":false,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","statusVal":"","statusType":"auto","x":1330,"y":740,"wires":[]},{"id":"7facd5a5.b37e9c","type":"function","z":"c473a0c1.c75468","name":"PUD cost calculator","func":"//https://www.snopud.com/Site/Content/Documents/rates/electricrates_010121.pdf\n/*\neffective date      $/kWh\n1 Oct 2017          0.10414\n1 Apr 2021          0.10118\n1 Apr 2022          0.09822\n1 Apr 2023          0.09527\n1 Apr 2024          0.09231\n1 Apr 2025          0.08935\n*/\n\n/*\nvar now = new Date(\"1/15/2021\");\nvar rate = -1;\n\n//var now = Date().getTime(); // 1501653935994\n//var from = new Date(\"02/08/2017\").getTime(); // gives 1486492200000\n//var to = new Date(\"05/08/2017\").getTime();\n\nif(now <= Date(\"1/4/2021\")) {\n   rate = 0.10414;\n}\n\nelse if(now <= Date(\"1/4/2022\")) {\n   rate = 0.10118;\n}\nelse if(now <= Date(\"1/4/2023\")) {\n   rate = 0.09822;\n}\nelse if(now <= Date(\"1/4/2024\")) {\n   rate = 0.09527;\n}\nelse if(now <= Date(\"1/4/2025\")) {\n   rate = 0.08935;\n}\n\n//msg.payload = msg.payload * rate;\nmsg.payload = rate;\nreturn msg;\n*/\n\nconst now = new Date().getTime();\nnode.warn(now)\nvar rate = -1;\n\nif(now <= new Date(\"2021-4-1\").getTime()) {\n  rate = 0.10414;\n}\nelse if(now <= new Date(\"2022-4-1\").getTime()) {\n  rate = 0.10118;\n}\nelse if(now <= new Date(\"2023-4-1\").getTime()) {\n  rate = 0.09822;\n}\nelse if(now <= new Date(\"2024-4-1\").getTime()) {\n  rate = 0.09527;\n}\nelse if(now <= new Date(\"2025-4-1\").getTime()) {\n  rate = 0.08935;\n}\n\nmsg.payload = msg.payload * rate;\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","x":1130,"y":900,"wires":[["c204bda8.68d478","f0ee9503.01a6e8"]]},{"id":"f0ee9503.01a6e8","type":"influxdb out","z":"c473a0c1.c75468","influxdb":"93285a9.26cb8a8","name":"AccumulatedCost_weekly","measurement":"rtl_433/Blueline_PowerCost_Monitor/AccumulatedCost_weekly","precision":"","retentionPolicy":"","database":"database","precisionV18FluxV20":"ms","retentionPolicyV18Flux":"","org":"organisation","bucket":"bucket","x":1370,"y":900,"wires":[]},{"id":"c204bda8.68d478","type":"debug","z":"c473a0c1.c75468","name":"","active":false,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","statusVal":"","statusType":"auto","x":1330,"y":860,"wires":[]},{"id":"93285a9.26cb8a8","type":"influxdb","hostname":"127.0.0.1","port":"8086","protocol":"http","database":"sensors","name":"sensors","usetls":false,"tls":"","influxdbVersion":"1.x","url":"http://localhost:8086","rejectUnauthorized":true}]`

I'm seeing the following error though. It appears that the hourly etc nodes are not being processed correctly.

[Colin]

@bkenobi

The rain data comes in as a running total of inches of precipitation. I put that directly into the DB

According to the Influx docs then something like this should give you the daily totals for January (or over any other period and time range by adjusting the parameters).
> SELECT DIFFERENCE(LAST("rainfall")) FROM "the_measurement" WHERE time >= '2021-01-01T00:00:00Z' AND time <= '2021-02-01T00:00:00Z' GROUP BY time(1d)
Look in particular at the Advanced Syntax section of that page.
The GROUP BY 1 day clause means it takes the samples in batches of 1 day, the LAST() says to take the last sample of each of the one day batches and the DIFFERENCE says to take the difference of each of those values. It should return an array of values, one for each day, containing the rainfall in that day.
I haven't tested it so no doubt it will take a bit of experimentation to get it right.

To cope with the wrap around all you have to do is to test for a negative value and if so add on the 'full house' value of the counter. So if that were a 16 bit counter you would have to add on 65536, just to that one sample.

I have taken a different approach with my rainfall. My sensor is on a 1-wire bus and I can easily sample it rapidly, so I read it every few seconds and note each time it increments and just store the value 0.6 each time it increments (each increment is 0.6mm). Then to show the rainfall over a period in Grafana I use the query
SELECT CUMULATIVE_SUM(SUM("value")) FROM "rain_increments" WHERE $timeFilter GROUP BY time($__interval)

[bkenobi]

@Colin

I have taken a different approach with my rainfall. My sensor is on a 1-wire bus and I can easily sample it rapidly, so I read it every few seconds and note each time it increments and just store the value 0.6 each time it increments (each increment is 0.6mm). Then to show the rainfall over a period in Grafana I use the query
SELECT CUMULATIVE_SUM(SUM("value")) FROM "rain_increments" WHERE $timeFilter GROUP BY time($__interval)

This is more or less what I was hoping to accomplish. The meter records the running total and sends that total every ~30 seconds. If the total has increased, I intend to write the increment at that time. This approach should work for any kind of meter that works this way (power, rain, water, etc). I was intending to create an hourly, weekly, etc databases only because with the use of a retention policy I could reduce the size of the database for longer term storage. I don't need to know the 30 second increments of rainfall from 10 years ago, but it might be nice to know for the last day or two.

I'm only using the code I posted because it was the best example of how to do this type of thing in NR and I don't have a better starting point. However, it's not working right out of the box so I'm having to rewrite things more than expected. Since I'm new to NR, I'm learning a lot about debug nodes!

Right now I'm trying to figure out why the data that comes in from the sensor seems to be working but the hourly does not. I just get an error and the flow never writes to the influxdb.

[Colin]

You are overcomplicating it. Let the database do the heavy lifting. Save the data to the database as it comes in into a measurement with, say, a four week retention policy, and with a Continuous Query that downsamples it to daily totals into a 'forever' retention policy. Then you will have the full dataset available for four weeks and daily forever. Don't worry about the database size, with one sample a day you could run for thousands of years before getting to anything that might be problematic. Influx is designed to store many millions of measurements.

If you want hourly tables or graphs or whatever then you can use database queries to give you whatever you want.

In fact I don't downscale my rainfall data at all, I keep it in a 'forever' retention policy. For me it works out at about 3000 to 5000 samples a year and I am over 70. Twenty years at that rate will still only be 100, 000 which is nothing, and in that time scale the technology will evolve quicker than my data will build up so it will be even more nothing by then. For my other measurements, temperature, wind direction/speed etc, some of which are sampled every 5 seconds, I keep it for a year and downscale it from there for longer term storage.

[bkenobi]

Sounds fairly simple. I'll try changing the code simply write the raw data as a difference as you suggested. That was the original thought but due to a lack of experience with NR I appear to have found an overly complicated approach.

I'll see what I can do and post what I come up with.

[bkenobi]

It appears there are a few ways to accomplish this task.

1. Trigger off of new MQTT data. Calculate difference between current data and last saved context and record to Influx. Save new data as old data for next round.
2. Trigger off of new MQTT data. Query Influxdb for last 2 saved values and calculate the difference. Save the difference to Influx.
3. Trigger off of MQTT data and just record the accumulated value to Influx. Use an Influx task to calculate the difference.

Since my sensors only report at ~30 second intervals, I can't use your approach, but it's not all that different what I can do. I'm currently doing the first part of option 3 (recording the raw accumulated data to Influx). However, the data rolls over at some point so if I do this, I have to figure out how to roll things correctly. Also, this likely takes a lot more storage since recording an increment periodically is less than recording a 10 year total rain each 30 seconds (exaggeration).

I started reading on saving to variables with different context level and it seems fairly easy (option 1). Option 2 seems fairly easy too, but then I'd be saving the raw data for no reason. I guess I could create a retention policy that it only stays around for a day or something. Option 3 seems great if you know how to use the database but I'm going to try option 1 for now as it is inline with the way I'm used to doing things.

[Colin]

Use option 1. It is very easy to calculate the difference in a function node in just a few lines of code. I will post an example later

[Colin]

Here is a function to give the difference from previous sample. It assumes that you have configured a persistent context store called "file". I expect you know how to do this but if not the see these docs. You need persistent context so it survives a node-red restart. It assumes you have a 16 bit counter, so wraps at 65536.

// Given an increasing value, calculates difference between current and previous payload
// Allows for inputs that wrap around back to zero
const wrapValue = 65536     // value for 16 bit counter
let last = context.get("last", "file")
if (typeof last === "undefined") last = msg.payload;
context.set("last", msg.payload, "file")
msg.payload = msg.payload - last
// allow for counter wrap around
if (msg.payload < 0) msg.payload += wrapValue
return msg;

and a test flow

[{"id":"fad734d0.13ddc","type":"inject","z":"37f5b818.697af8","name":"","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"7","payloadType":"num","x":150,"y":180,"wires":[["f229afb8.f6d158"]]},{"id":"f229afb8.f6d158","type":"function","z":"37f5b818.697af8","name":"","func":"// Given an increasing value, calculates difference between current and previous payload\n// Allows for inputs that wrap around back to zero\nconst wrapValue = 65536     // value for 16 bit counter\nlet last = context.get(\"last\", \"file\")\nif (typeof last === \"undefined\") last = msg.payload;\ncontext.set(\"last\", msg.payload, \"file\")\nmsg.payload = msg.payload - last\n// allow for counter wrap around\nif (msg.payload < 0) msg.payload += wrapValue\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","x":320,"y":200,"wires":[["4f08b62f.1b2be"]]},{"id":"4f08b62f.1b2be","type":"debug","z":"37f5b818.697af8","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","statusVal":"","statusType":"auto","x":470,"y":200,"wires":[]},{"id":"ccc63c88.e8043","type":"inject","z":"37f5b818.697af8","name":"","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"10","payloadType":"num","x":150,"y":220,"wires":[["f229afb8.f6d158"]]},{"id":"5dda98a7.e6c88","type":"inject","z":"37f5b818.697af8","name":"","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"65535","payloadType":"num","x":150,"y":260,"wires":[["f229afb8.f6d158"]]},{"id":"8ce27d41.02a84","type":"inject","z":"37f5b818.697af8","name":"","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"0","payloadType":"num","x":150,"y":140,"wires":[["f229afb8.f6d158"]]}]

[Colin]

Thinking about it you probably don't want anything returned if it is zero, in which case replace the last line with
if (msg.payload > 0) return msg

[bkenobi]

I was going to post this last night but it was already late when I got it working. I implemented basically the same approach as your code.

var Delta = 0;
var OldValue = flow.get("OldReading");
if (OldValue===undefined) {
    OldValue = 0;
    // use current value?  Or look up last value in kWh database?
}

if (msg.payload == OldValue) {
    // No change, don't update database
    
} else if (msg.payload > OldValue) {
    // Calculated Delta, update database
    Delta = msg.payload - OldValue;
} else {
    // Roll Over, calculate delta, update database
    Delta = msg.payload + RollOver - OldValue;
}

flow.set("OldReading", msg.payload);

if (Delta > 0) {
    msg.payload = Delta;
    return msg;
}

The thing I was debating about was what happens when the flow is reset and I lose the old value. I like your approach of saving to file. I'll have to see what that will take since zeroing the old value results in a spike in the data and using the current value loses the data in the gap. The appropriate action would be to keep the last value in a more stable location which you have by writing to a file.

[bkenobi]

The power meter sensor resets at 65535 which is about every 14 days during my highest usage. The only way I would lose data if I save the last value to file is if the server were offline for 14+ days. That seems like a small risk so should be fine.

My weather station resets at 99.99 inches of rain. While Seattle does get a lot of rain, that's still months before a reset.

[bkenobi]

I changed my code to match your approach. I am not sure if the context is set to store to memory of file though. I have "file" in the set and get statements, but according to the guide I'm using, it looks like I may need to change the settings.js file?

A guide to understanding 'Persistent Context'

This is something I wrote up after playing with the context options. I've filed a PR with some of this added to the Node-RED documentation but thought I'd release it here also. I hope people find it useful. Paul A guide to understanding 'Persistent Context' Prior to Node-RED v0.19.0, data could be stored in context as a global, flow or node. This was stored in memory and would be reset with a restart of Node-RED. As of v.0.19.0 you can now store node, flow and global context data in memory O…

    contextStorage: {
    	default    : { module: "memory" },
		storeInFile: { module: "localfilesystem"},
		memoryOnly : { module: "memory" }
    },

Perhaps that's the default setting since I'm not seeing an error and it appears that the value is getting stored.