Calculate up and downtime

General
1

Hi,

Im trying to make a % up time of a machine. The machine sends a MQTT signal to the broker where node-red is running. The signal gets inserted into a mysql database. When the machine is on it publish a 1. When the machine is turned off it publish a 0. Is there a way is node-red to calculate the % on based on the daily time on + off?

I tried to make it myself and got this from the internet:

[{"id":"1d29d505.98215b","type":"mqtt in","z":"f61eaed1.e45da","name":"","topic":"pers77_status","qos":"2","broker":"d0e4f151.5ec18","x":130,"y":260,"wires":[["8bd74e88.dc306","dbc06091.377bd","71de4e59.9991d"]]},{"id":"8bd74e88.dc306","type":"function","z":"f61eaed1.e45da","name":"Set start time","func":"if (msg.topic === \"0\") {\nvar now = new Date();   // current time\nflow.set(\"startTime\", now);\n} else {\n   return [ msg, null ];\n}\nreturn msg;","outputs":1,"noerr":0,"x":330,"y":260,"wires":[["1c1caf69.bd00d1"]]},{"id":"dbc06091.377bd","type":"function","z":"f61eaed1.e45da","name":"Calc difference ms","func":"if (msg.topic === \"1\") {\nvar now = new Date();\nmsg.payload = now - flow.get(\"startTime\")\n} else {\n   return [ msg, null ];\n}\nreturn msg;","outputs":1,"noerr":0,"x":350,"y":340,"wires":[["49a1c573.c8644c"]]},{"id":"49a1c573.c8644c","type":"debug","z":"f61eaed1.e45da","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","x":550,"y":340,"wires":[]},{"id":"71de4e59.9991d","type":"debug","z":"f61eaed1.e45da","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","x":330,"y":300,"wires":[]},{"id":"1c1caf69.bd00d1","type":"debug","z":"f61eaed1.e45da","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","x":550,"y":260,"wires":[]},{"id":"d0e4f151.5ec18","type":"mqtt-broker","z":"","name":"MQTT_Broker","broker":"192.168.16.76","port":"1883","clientid":"","usetls":false,"compatmode":true,"keepalive":"60","cleansession":true,"birthTopic":"","birthQos":"0","birthPayload":"","closeTopic":"","closeQos":"0","closePayload":"","willTopic":"","willQos":"0","willPayload":""}]

i basicly need the total time the machine is on 1 and the total time the machine is on 0.

Im not sure if i presented all the information. If you need anymore information, please let me know.

2

so you want to know the percentage of the time the machine is on in a 24 hour period.

  1. Can the maching be turned on and off multiple times per day?
  2. Will this be done once a day or do you need a 'so far today' pecentage?
  3. what happens if the machine is running when the day changes? (damn edge cases)

If you only need to calculate it once per day then at the end of the day, you can get each start-time/stop-time pair, subtract the start-time from the stop-time and add it to the total run time. Next calculate what that value is of 24 hours.

If you need a running percentage, I would store the start time in a flow variable as well as in the db and when a stop comes thru, write it to the db and then compute the latest on time using the flow variable you saved, Add tehm to a running total and calculate how much time has elapsed so far today and figure the percentage.

Hope this helps a little :smile:

3

It needs te be a running percentage. I found and interval register node and got this so far:

[{"id":"79ce684e.6b6da8","type":"function","z":"1f216cc0.281a93","name":"INSERT statement","func":"if (msg.payload === 1) {\n   msg.topic = \"INSERT INTO `IOT_DATA` ( `DATA_BRON`,`DATA_SOORT`,`DATA_WAARDE`) VALUES ('77','aan',\" + msg.payload + \")\";\n} else {\n   msg.topic = \"INSERT INTO `IOT_DATA` ( `DATA_BRON`,`DATA_SOORT`,`DATA_WAARDE`) VALUES ('77','uit',\" + msg.payload + \")\";\n}\n\nreturn msg;","outputs":1,"noerr":0,"x":810,"y":360,"wires":[["933216aa.2dc248","a4481e1e.3b5b9"]]},{"id":"933216aa.2dc248","type":"debug","z":"1f216cc0.281a93","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","x":1050,"y":360,"wires":[]},{"id":"9eb082e8.fa622","type":"mqtt in","z":"1f216cc0.281a93","name":"","topic":"pers77_status","qos":"2","broker":"d0e4f151.5ec18","x":70,"y":360,"wires":[["79ce684e.6b6da8","54c828cf.882268","3c3ee011.f1b88"]]},{"id":"54c828cf.882268","type":"function","z":"1f216cc0.281a93","name":"Color","func":"msg.color = (msg.payload === 0)?\"lime\":\"red\";\nreturn msg;","outputs":1,"noerr":0,"x":770,"y":420,"wires":[["4016442b.c9c95c"]]},{"id":"4016442b.c9c95c","type":"ui_text","z":"1f216cc0.281a93","group":"ef52ec43.10754","order":1,"width":"4","height":"2","name":"","label":"Aan/uit","format":"<font color={{msg.color}} ><i class=\"fa fa-circle\" style=\"font-size:32px;\"></i></font>","layout":"row-left","x":1040,"y":480,"wires":[]},{"id":"3c3ee011.f1b88","type":"function","z":"1f216cc0.281a93","name":"Generate spike","func":"var interval = setInterval(function() {\n    clearInterval(interval);\n\tnode.send({payload: 0});\n}, msg.payload); \n\nreturn null;","outputs":1,"noerr":0,"x":160,"y":520,"wires":[["20746ea6.68e8e2"]]},{"id":"20746ea6.68e8e2","type":"interval-length","z":"1f216cc0.281a93","format":"mills","bytopic":false,"minimum":"","maximum":"","window":"","timeout":false,"msgTimeout":"","minimumunit":"secs","maximumunit":"msecs","windowunit":"secs","reset":false,"startup":false,"msgField":"payload","timestampField":"timestamp","repeatTimeout":false,"name":"","x":360,"y":520,"wires":[["79ce684e.6b6da8","2a1fad18.a78762"],[]]},{"id":"2a1fad18.a78762","type":"debug","z":"1f216cc0.281a93","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","x":640,"y":540,"wires":[]},{"id":"d0e4f151.5ec18","type":"mqtt-broker","z":"","name":"MQTT_Broker","broker":"192.168.16.76","port":"1883","clientid":"","usetls":false,"compatmode":true,"keepalive":"60","cleansession":true,"birthTopic":"","birthQos":"0","birthPayload":"","closeTopic":"","closeQos":"0","closePayload":"","willTopic":"","willQos":"0","willPayload":""},{"id":"ef52ec43.10754","type":"ui_group","z":"","name":"Pers 77","tab":"6b96da3f.ce1c04","order":1,"disp":true,"width":"24","collapse":false},{"id":"6b96da3f.ce1c04","type":"ui_tab","z":"","name":"Machine data","icon":"dashboard","order":1,"disabled":false,"hidden":false}]

Only the if statement is always false

4

  1. msg.payload probably isnt === 1
    To prove this, put a debug node after the MQTT (before the input of the function)
    it may be ==1 or === "1"
    read this to understand why

  2. on the SQL stuff, pretty sure the back ticks will cause you issues (use none or square brackets)

your code...
msg.topic = "INSERT INTO `IOT_DATA` ( `DATA_BRON`,`DATA_SOORT`,`DATA_WAARDE`) VALUES ('77','aan'," + msg.payload + ")";

This may be better (though you didnt specify type of database)...
msg.topic = "INSERT INTO [IOT_DATA] ( DATA_BRON, DATA_SOORT, DATA_WAARDE) VALUES ('77','aan'," + msg.payload + ")";

Lastly, if you are putting stoppages into a databse, then qhy not simply create a SQL statement that summs up all downtime from your entries?

e.g. SELECT sum(downtime) FROM [TABLE] WHERE timestamp between @start AND @end

As for getting percentage - simple maths.