Hello everyone i want ask if there's someone can help me in node-red flows i have msg.Payload is a counter and i want to save the total of this cumulate for each 8 hours.
please any one has an ideas to help me
thank you
You can use context to hold any value you want - so you can accumulate data in a context variable - see the doc Working with context : Node-RED.
1 Like
For what purpose do you want to save it, and for how long do you need to keep it?
What are you counting?
1 Like
the data is about time of stop machine and time of working and i want to send it to SQL server so i want to know how much time is it stop and how much are working i have a bool variable indicate state of machine
What exactly do you want stored in the database? To answer that question you need to know what the data will be used for in the future.
1 Like
Just the duration of each state how much the false input take and how much the true state take
somthing like this
There is no point saving the start time, end time and duration, as the duration can be calculated from the start and end times.
Personally I would write the state to the database each time it changes, but if you don't want to do that then save the start time to the context each time it starts, and then when it ends write the start and end time to the database, or the start time and interval, or the end time interval, whichever fits your requirements best.
1 Like
There are also several previous threads that may help. like this How to calculate machine running time
and Cumulative running time
1 Like
Thank you for your help but i still can't get my goal so I ask for your help with this flow what i need is to count the true and false state but i want the total of true or false until change the state so if the payload is true two time or 3 ... the script count but don't send it in the debug because i want to save this in a database and i don't to send every moment to the database so i want just the counting when the payload change from true to false or false to true that will help me to have an idea about the downtime and running time
[{"id":"336fad55.d74c02","type":"tab","label":"Flow 6","disabled":false,"info":""},{"id":"e3618232.4b2f7","type":"inject","z":"336fad55.d74c02","name":"","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"true","payloadType":"bool","x":360,"y":180,"wires":[["6708b421.47e96c"]]},{"id":"943e7488.cc2928","type":"inject","z":"336fad55.d74c02","name":"","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"false","payloadType":"bool","x":360,"y":260,"wires":[["6708b421.47e96c"]]},{"id":"6708b421.47e96c","type":"function","z":"336fad55.d74c02","name":"","func":"var dontime,uptime;\nvar countUp=0;\nvar countDw=0;\n\nif(msg.payload == true)\n{\n countUp+=1;\n \n \n}\nif(msg.payload == false)\n{\n countDw+=1;\n \n \n}\n\nvar a=context.get('countUp') ;\nvar b=context.get('countDw') ;\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","x":620,"y":220,"wires":[["e4aaffb.e9e2f"]]},{"id":"e4aaffb.e9e2f","type":"debug","z":"336fad55.d74c02","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","statusVal":"","statusType":"auto","x":850,"y":220,"wires":[]},{"id":"93c7073.7f0c8f8","type":"counter","z":"336fad55.d74c02","inc":1,"name":"","x":760,"y":420,"wires":[["527e488a.6bca88"]]},{"id":"6dec57af.0800a8","type":"counter","z":"336fad55.d74c02","inc":1,"name":"","x":760,"y":480,"wires":[["43649482.bca16c"]]},{"id":"ad8c4b3a.886db8","type":"inject","z":"336fad55.d74c02","name":"","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"true","payloadType":"bool","x":450,"y":400,"wires":[["66e744f0.9439ac"]]},{"id":"4156ccd7.f8ae64","type":"inject","z":"336fad55.d74c02","name":"","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"false","payloadType":"bool","x":490,"y":460,"wires":[["66e744f0.9439ac"]]},{"id":"527e488a.6bca88","type":"debug","z":"336fad55.d74c02","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","statusVal":"","statusType":"auto","x":935.933349609375,"y":416.9333190917969,"wires":[]},{"id":"43649482.bca16c","type":"debug","z":"336fad55.d74c02","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","statusVal":"","statusType":"auto","x":950,"y":480,"wires":[]},{"id":"66e744f0.9439ac","type":"switch","z":"336fad55.d74c02","name":"","property":"payload","propertyType":"msg","rules":[{"t":"true"},{"t":"false"}],"checkall":"true","repair":false,"outputs":2,"x":610,"y":440,"wires":[["93c7073.7f0c8f8"],["6dec57af.0800a8"]]}]
I don't understand what you are trying to count. You seem to suggest you want to record the number of times it stops and starts, but I thought you wanted to record the running time and downtime. Please tell us exactly what you want to put into the database and when it has to be added to the database.
If it is the running time and downtime you want here is a simple example that records the start time and passes on a message containing start and stop time each time the process stops.
[{"id":"4c121f0f.9a60d8","type":"inject","z":"84405ff5.25fa6","name":"","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"true","payloadType":"bool","x":103.99998474121094,"y":1149.0000610351562,"wires":[["8992fd64.d42c9"]]},{"id":"57dc4559.4d6394","type":"inject","z":"84405ff5.25fa6","name":"","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"false","payloadType":"bool","x":103.99998474121094,"y":1209.0000610351562,"wires":[["8992fd64.d42c9"]]},{"id":"3b02ac7c.2e776c","type":"debug","z":"84405ff5.25fa6","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","statusVal":"","statusType":"auto","x":410,"y":1180,"wires":[]},{"id":"8992fd64.d42c9","type":"function","z":"84405ff5.25fa6","name":"","func":"if (msg.payload) {\n // payload is true so save start time\n startTime = new Date()\n context.set(\"startTime\", startTime)\n msg = null // don't send a message\n} else {\n // payload is false so send start and end time\n startTime = context.get(\"startTime\") || 0\n if ( startTime === 0) {\n // no start time recorded so must be first time through so ignore it\n msg = null\n } else {\n msg.payload = {startTime: startTime, stopTime: new Date()}\n // clear start time\n context.set(\"startTime\", 0)\n }\n}\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","x":260,"y":1180,"wires":[["3b02ac7c.2e776c"]]}]
1 Like
Hi thank you so much for your time
What I want to add into database is the date when the machine is stoping and when it start running
My maching send a true when it's run and false when it's stop so I want to send to database when it's start and stop and not every state because the machine send every 2 seconds the state so it too much data so I want just when the state change the function send me the date and time with the last topic true or false or on Off
I am sorry for my English and thank you for your support
I try this one it's perfectly run @Colin
Just the start time update when true is inject what I want is if the true is injuct the function compare the courant state with the previous one if it's the same the function will send nothing but if it's false the will send the start and the end of the state
So I can know how much take the false state and the true state how much is the machine is running because my machine send the state every 2 seconds
Add an RBE node before the function. The RBE node only passes on a message when the value changes.
1 Like
Hi @Colin thank you so much for your time and you are really helped me
I just want ask you: is it possible to separate the tow date {"startTime":"2021-03-22T23:17:04.963Z","StopeTime":"2021-03-22T23:17:09.879Z"} to insert it to a database i try to use a split node with Jason from but it's added to the database in two lines and i want in one line
What type of database is it?
Which node are you using to write to it (possibly node-red-contrib-something for example)?
Show us an example of the SQL query that you want to pass to the database.
1 Like
Ok i want to send this to dateTime to SQL Server it's work but the message sends in one line and in one column so i want to separate it to the two msg with propre topic
Well you answered one of my questions, but not these two
i use this one
@gomake/node-red-contrib-mssql-jb
i try it with the node that you give me @Colin so i want to send this to the database spratly
[{"id":"bec435f1.8aacc8","type":"tab","label":"Flow 8","disabled":false,"info":""},{"id":"4def3b62.148184","type":"inject","z":"bec435f1.8aacc8","name":"","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"true","payloadType":"bool","x":290,"y":180,"wires":[["6ce2f189.e78b5"]]},{"id":"61db9526.7fe8cc","type":"inject","z":"bec435f1.8aacc8","name":"","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"false","payloadType":"bool","x":290,"y":240,"wires":[["6ce2f189.e78b5"]]},{"id":"a290811d.a5e23","type":"debug","z":"bec435f1.8aacc8","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","statusVal":"","statusType":"auto","x":910,"y":140,"wires":[]},{"id":"ad81394.1bf42c8","type":"function","z":"bec435f1.8aacc8","name":"","func":"\nif (msg.payload) {\n // payload is true so save start time\n startTime = new Date()\n context.set(\"startTime\", startTime)\n msg = null // don't send a message\n} else {\n // payload is false so send start and end time\n startTime = context.get(\"startTime\") || 0\n if ( startTime === 0) {\n // no start time recorded so must be first time through so ignore it\n msg = null\n } else {\n var stopTime =new Date()\n \n msg.payload = {startTime : startTime , StopTime : stopTime}\n // clear start time\n context.set(\"startTime\", 0)\n }\n}\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","x":646.0000152587891,"y":210.99993896484375,"wires":[["a290811d.a5e23","7a3ec9da.225f38"]]},{"id":"6ce2f189.e78b5","type":"rbe","z":"bec435f1.8aacc8","name":"","func":"rbe","gap":"","start":"","inout":"out","property":"payload","x":450,"y":200,"wires":[["ad81394.1bf42c8","f753d922.dabc98"]]},{"id":"f753d922.dabc98","type":"debug","z":"bec435f1.8aacc8","name":"","active":false,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","statusVal":"","statusType":"auto","x":580,"y":120,"wires":[]},{"id":"c713c59a.8542d8","type":"MSSQL","z":"bec435f1.8aacc8","mssqlCN":"df8c0b88.91b0a8","name":"MSSQL","query":"","outField":"payload","x":880,"y":320,"wires":[["be343908.c57268"]]},{"id":"be343908.c57268","type":"debug","z":"bec435f1.8aacc8","name":"","active":true,"console":"false","complete":"payload","x":1050,"y":320,"wires":[]},{"id":"28c4f4a4.9da41c","type":"function","z":"bec435f1.8aacc8","name":"Function","func":"d = new Date(),\ndformat = [d.getMonth()+1,\n d.getDate(),\n d.getFullYear()].join('/')+' '+\n [d.getHours(),\n d.getMinutes(),\n d.getSeconds()].join(':');\n\npld = \"INSERT INTO [Dev].[dbo].[DataS] \"\npld = pld + \"(Topic, Payload, Timestamp) \"\npld = pld + \"VALUES ('\" + msg.topic + \"', '\" + msg.payload + \"', '\" + dformat + \"')\"\n\nmsg.topic = ''\nmsg.payload = pld\nreturn msg;\n\n\n\n\n","outputs":1,"noerr":0,"initialize":"","finalize":"","x":740,"y":340,"wires":[["c713c59a.8542d8"]]},{"id":"7a3ec9da.225f38","type":"json","z":"bec435f1.8aacc8","name":"","property":"payload","action":"","pretty":false,"x":590,"y":280,"wires":[["28c4f4a4.9da41c"]]},{"id":"3e82b0d9.b2c6d","type":"split","z":"bec435f1.8aacc8","name":"","splt":",","spltType":"str","arraySplt":1,"arraySpltType":"len","stream":false,"addname":"","x":850,"y":260,"wires":[[]]},{"id":"df8c0b88.91b0a8","type":"MSSQL-CN","name":"Dev","server":"localhost","encyption":false,"database":"Dev"}]
That node has not been updated in two years and its github repository has been deleted, apparently, so I recommend not using that one. I don't use mssql but I have seen node-red-contrib-mssql-plus often recommended here
This is not an mssql forum and I have never used it. You need to work out the queries you want to send to it. We can then help you build that query to send to the database node.
1 Like
