Hello everybody I have a problem and hope you are help to me like before.
Im saving data in Sqlite db.And I have to visualize this data in dashboard. Is it possible visualize this times, as you see in photo. high/low pin situation time calculating.
For example : 28.02.2022 : Total : 56 second with chart graphic ?
If yes please help to me.
are you able to get the result of above query through Node-Red sqlite node ?
then you can store the values into flow.context and use the below function.
[{"id":"1d59c3b7653a0200","type":"function","z":"f0c78620b090a94c","name":"Time-Diff","func":"var one = flow.get('one1') || 0;\nvar zero = flow.get('zero0') || 0;\nvar diffMS=zero-one;\nvar diffm = diffMS/60000;\nmsg.payload1 = Math.round(diffm);\nreturn msg","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":1000,"y":120,"wires":[["116d5d9ecffa4b1c"]]}]
Dear friend. I have used sqlite node, but didnt know the codes in function node.
Datas are saving data base, Im using sqlite node, but I need codes for getting this data from database, just total pin high (1) situation time.
My node is like this
[{"id":"5bc4fcf5.332164","type":"subflow","name":"Subflow 1","info":"","category":"","in":[{"x":60,"y":80,"wires":[{"id":"2c29ca9d.1f3726"}]}],"out":[{"x":920,"y":100,"wires":[{"id":"26fe58d.da0d2a8","port":0}]},{"x":540,"y":120,"wires":[{"id":"c3cccc67.eba87","port":0}]}]},{"id":"2c29ca9d.1f3726","type":"function","z":"5bc4fcf5.332164","name":"","func":"\nreturn msg;","outputs":1,"noerr":0,"x":200,"y":80,"wires":[["c3cccc67.eba87","dc32c467.fb1168"]]},{"id":"dc32c467.fb1168","type":"switch","z":"5bc4fcf5.332164","name":"allow only 1 ","property":"payload","propertyType":"msg","rules":[{"t":"eq","v":"1","vt":"num"},{"t":"else"}],"checkall":"true","repair":false,"outputs":2,"x":390,"y":80,"wires":[["90e7351e.a17e68"],["54e68c10.45d2f4"]]},{"id":"c3cccc67.eba87","type":"trigger","z":"5bc4fcf5.332164","op1":"","op2":"Kapı 30 saniyedir açık!!","op1type":"nul","op2type":"str","duration":"30","extend":false,"units":"s","reset":"1","bytopic":"all","name":"","x":390,"y":120,"wires":[[]]},{"id":"90e7351e.a17e68","type":"change","z":"5bc4fcf5.332164","name":"High","rules":[{"t":"set","p":"bc","pt":"msg","to":"orange","tot":"str"},{"t":"set","p":"status","pt":"msg","to":"Kapalı","tot":"str"}],"action":"","property":"","from":"","to":"","reg":false,"x":590,"y":80,"wires":[["26fe58d.da0d2a8"]]},{"id":"54e68c10.45d2f4","type":"change","z":"5bc4fcf5.332164","name":"Low","rules":[{"t":"set","p":"bc","pt":"msg","to":"purple","tot":"str"},{"t":"set","p":"status","pt":"msg","to":"Açık","tot":"str"}],"action":"","property":"","from":"","to":"","reg":false,"x":590,"y":120,"wires":[["26fe58d.da0d2a8"]]},{"id":"26fe58d.da0d2a8","type":"function","z":"5bc4fcf5.332164","name":"","func":"\nreturn msg;","outputs":1,"noerr":0,"x":780,"y":100,"wires":[[]]},{"id":"3c7c2323.4c861c","type":"debug","z":"978d4790.f6ab88","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","x":1130,"y":300,"wires":[]},{"id":"c480b490.309628","type":"ui_chart","z":"978d4790.f6ab88","name":"","group":"e78dad9d.fa084","order":4,"width":"18","height":"10","label":"mySQL data","chartType":"line","legend":"true","xformat":"HH:mm:ss","interpolate":"bezier","nodata":"","dot":false,"ymin":"","ymax":"","removeOlder":"2","removeOlderPoints":"1000","removeOlderUnit":"3600","cutout":0,"useOneColor":false,"colors":["#1f77b4","#aec7e8","#ff7f0e","#2ca02c","#98df8a","#d62728","#ff9896","#9467bd","#c5b0d5"],"useOldStyle":false,"x":1150,"y":360,"wires":[[]]},{"id":"583b4d14.7fce94","type":"sqlite","z":"978d4790.f6ab88","mydb":"e35ee04b.a8bff","sqlquery":"msg.topic","sql":"","name":"ip_cekili","x":780,"y":360,"wires":[["8eab3557.2a9e78"]]},{"id":"4578ba13.421184","type":"ui_button","z":"978d4790.f6ab88","name":"","group":"c55d32a2.85a79","order":9,"width":"3","height":"1","passthru":false,"label":"B5 Kapı - 1 {{msg.status}}","color":"","bgcolor":"{{bc}}","icon":"","payload":"1","payloadType":"num","topic":"topic","x":570,"y":320,"wires":[[]]},{"id":"3581ace4.658574","type":"dsm","z":"978d4790.f6ab88","name":"sensör_durumu","sm_config":"{\n \"triggerInput\": \"payload\",\n \"currentState\": \"running\",\n \"states\": {\n \"stopped\": {\n \"0\": \"running\"\n },\n \"running\": {\n \"1\": \"stopped\"\n }\n },\n \"methods\": {\n \"onBeforeTransition\": \"msg.pyload = msg.payload.toString();\",\n \"1\": \"sta.fill='green';msg.background='red';msg.label='S';\",\n \"0\": \"sta.fill='red';msg.background='green';msg.label='S';\"\n }\n}","x":370,"y":380,"wires":[["bb9b7d97.090aa"]]},{"id":"bb9b7d97.090aa","type":"template","z":"978d4790.f6ab88","name":"","field":"topic","fieldType":"msg","format":"handlebars","syntax":"mustache","template":"INSERT INTO 'sensor_durumu' ('durum','epoc') VALUES ({{payload}},{{pyload}});","output":"str","x":560,"y":400,"wires":[["583b4d14.7fce94"]]},{"id":"e3ba3cdb.5d9ad","type":"rpi-gpio in","z":"978d4790.f6ab88","name":"","pin":"40","intype":"down","debounce":"25","read":false,"x":170,"y":340,"wires":[["3581ace4.658574","510c4a13.094294"]]},{"id":"510c4a13.094294","type":"subflow:5bc4fcf5.332164","z":"978d4790.f6ab88","name":"Pin Status Monitoring","x":360,"y":340,"wires":[["4578ba13.421184"],["e4574aad.de7308"]]},{"id":"e4574aad.de7308","type":"ui_toast","z":"978d4790.f6ab88","position":"dialog","displayTime":"","highlight":"","outputs":1,"ok":"OK","cancel":"","topic":"Uyarı","name":"","x":570,"y":360,"wires":[[]]},{"id":"8eab3557.2a9e78","type":"function","z":"978d4790.f6ab88","name":"Time-Diff","func":"var one = flow.get('one1') || 0;\nvar zero = flow.get('zero0') || 0;\nvar diffMS=zero-one;\nvar diffm = diffMS/60000;\nmsg.payload1 = Math.round(diffm);\nreturn msg","outputs":1,"noerr":0,"x":940,"y":340,"wires":[["c480b490.309628","3c7c2323.4c861c"]]},{"id":"e78dad9d.fa084","type":"ui_group","z":"","name":"Home","tab":"6f5ce300.48f7bc","order":10,"disp":true,"width":"18","collapse":false},{"id":"e35ee04b.a8bff","type":"sqlitedb","z":"","db":"/var/www/html/database/kapi","mode":"RWC"},{"id":"c55d32a2.85a79","type":"ui_group","z":"","name":"Orginal","tab":"6f5ce300.48f7bc","order":9,"disp":true,"width":"6","collapse":false},{"id":"6f5ce300.48f7bc","type":"ui_tab","z":"","name":"Mercedes-Benz Türk AŞ Kamyon Fabrikası Hol Kapıları","icon":"dashboard","order":1}]
i dont use sqlite but have mysql. the logic should be same. (not sure).
build a query to get the time field for pin high position, hope you will sort the data to get required result. as per the screenshot you have posted i assume you are only capturing changes in the pin position and need the total duration when pin was in high position.
then use my function code to get the difference of time
you need to store the values into flow context.
How can I store them, could you please show me ? Then I will use your function right ?
Normally Im saving data into SQLite db
i will wait for people with sqlite nodes to respond.
What type of graph?
How do you decide which two rows go together?
What if the second reading goes to the next day?
What if a reading is missed?
Have you thought about altering the code that writes to the database to have one row per on/off time. The table would have
ID, start-time, end-time, duration, current-gpio-status,gpio-number
When a gpio is turned on, you write out a row with the start-time. When the gpio is turned off you get the current time then retrieve the prior row and calculate the duration then update the row.
The in NR you just need to get the date/time (probably the start-time) and duration and send them to a graph.
@wtrk similar to what Paul is suggesting, something like this .
I log fdate and tdate both in a single row, and calculate the difference there itself (dtmin)
Dear @smanjunath211 friend I want completly like you shared photo.Could you please show me your complete flow.
As @zenofmud suggest you would be better storing the start time(epoch) and end time(durum). Then when you want to recall your data from db it would be easier.
I show an example below, it will need work, but might put you on a better track.
[{"id":"c8f65c8c.8326c","type":"inject","z":"bf9e1e33.030598","name":"","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"1","payloadType":"num","x":130,"y":1500,"wires":[["322f98cf.1783f"]]},{"id":"322f98cf.1783f","type":"switch","z":"bf9e1e33.030598","name":"","property":"payload","propertyType":"msg","rules":[{"t":"eq","v":"1","vt":"num"},{"t":"eq","v":"0","vt":"num"}],"checkall":"false","repair":false,"outputs":2,"x":240,"y":1560,"wires":[["91465b8.46fae28"],["5677043f.1a324c"]]},{"id":"f133b5ce.23dbd","type":"inject","z":"bf9e1e33.030598","name":"","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"0","payloadType":"num","x":130,"y":1600,"wires":[["322f98cf.1783f"]]},{"id":"91465b8.46fae28","type":"change","z":"bf9e1e33.030598","name":"","rules":[{"t":"set","p":"payload","pt":"msg","to":"","tot":"date"},{"t":"set","p":"topic","pt":"msg","to":"epoch","tot":"str"}],"action":"","property":"","from":"","to":"","reg":false,"x":410,"y":1540,"wires":[["4dd02718.cd69e8"]]},{"id":"5677043f.1a324c","type":"change","z":"bf9e1e33.030598","name":"","rules":[{"t":"set","p":"payload","pt":"msg","to":"","tot":"date"},{"t":"set","p":"topic","pt":"msg","to":"durum","tot":"str"},{"t":"set","p":"complete","pt":"msg","to":"true","tot":"bool"}],"action":"","property":"","from":"","to":"","reg":false,"x":410,"y":1580,"wires":[["4dd02718.cd69e8"]]},{"id":"4dd02718.cd69e8","type":"join","z":"bf9e1e33.030598","name":"","mode":"custom","build":"object","property":"payload","propertyType":"msg","key":"topic","joiner":"\\n","joinerType":"str","accumulate":false,"timeout":"","count":"","reduceRight":false,"reduceExp":"","reduceInit":"","reduceInitType":"","reduceFixup":"","x":570,"y":1560,"wires":[["3fda863b.17afa2","b67ce6c3.2e1688"]]},{"id":"3fda863b.17afa2","type":"template","z":"bf9e1e33.030598","name":"","field":"topic","fieldType":"msg","format":"handlebars","syntax":"mustache","template":"INSERT INTO 'sensor_durumu' ('durum','epoc') VALUES ({{payload.durum}},{{payload.epoch}});","output":"str","x":750,"y":1560,"wires":[["2ae33415.ea28ec"]]},{"id":"b67ce6c3.2e1688","type":"function","z":"bf9e1e33.030598","name":"","func":"msg.payload = (msg.payload.durum - msg.payload.epoch)/1000;\nmsg.topic = \"time diff\";\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","x":770,"y":1480,"wires":[["c4680fa0.bc3068","4b0d4a88.3bac64"]]},{"id":"2ae33415.ea28ec","type":"debug","z":"bf9e1e33.030598","name":"store in db","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","statusVal":"","statusType":"auto","x":960,"y":1560,"wires":[]},{"id":"c4680fa0.bc3068","type":"ui_chart","z":"bf9e1e33.030598","name":"","group":"e71e459a.47b628","order":4,"width":"18","height":"10","label":"mySQL data","chartType":"line","legend":"true","xformat":"HH:mm:ss","interpolate":"bezier","nodata":"","dot":false,"ymin":"","ymax":"","removeOlder":"2","removeOlderPoints":"1000","removeOlderUnit":"3600","cutout":0,"useOneColor":false,"useUTC":false,"colors":["#1f77b4","#aec7e8","#ff7f0e","#2ca02c","#98df8a","#d62728","#ff9896","#9467bd","#c5b0d5"],"outputs":1,"useDifferentColor":false,"className":"","x":960,"y":1480,"wires":[[]]},{"id":"4b0d4a88.3bac64","type":"debug","z":"bf9e1e33.030598","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","statusVal":"","statusType":"auto","x":1000,"y":1440,"wires":[]},{"id":"e71e459a.47b628","type":"ui_group","name":"Home","tab":"6f5ce300.48f7bc","order":10,"disp":true,"width":"18","collapse":false},{"id":"6f5ce300.48f7bc","type":"ui_tab","name":"Mercedes-Benz Türk AŞ Kamyon Fabrikası Hol Kapıları","icon":"dashboard","order":1}]
Dear @E1cid I know but Im lookng for the easy and basic way, because I dont know how can I create epoc also storing data etc.
just follow his example, it is straight forward. replace inject nodes with your flow output
Hello dear @E1cid I have done your flow. I need a little bit more help.
this is my flow
[{"id":"5bc4fcf5.332164","type":"subflow","name":"Subflow 1","info":"","category":"","in":[{"x":60,"y":80,"wires":[{"id":"2c29ca9d.1f3726"}]}],"out":[{"x":920,"y":100,"wires":[{"id":"26fe58d.da0d2a8","port":0}]},{"x":540,"y":120,"wires":[{"id":"c3cccc67.eba87","port":0}]}]},{"id":"2c29ca9d.1f3726","type":"function","z":"5bc4fcf5.332164","name":"","func":"\nreturn msg;","outputs":1,"noerr":0,"x":200,"y":80,"wires":[["c3cccc67.eba87","dc32c467.fb1168"]]},{"id":"dc32c467.fb1168","type":"switch","z":"5bc4fcf5.332164","name":"allow only 1 ","property":"payload","propertyType":"msg","rules":[{"t":"eq","v":"1","vt":"num"},{"t":"else"}],"checkall":"true","repair":false,"outputs":2,"x":390,"y":80,"wires":[["90e7351e.a17e68"],["54e68c10.45d2f4"]]},{"id":"c3cccc67.eba87","type":"trigger","z":"5bc4fcf5.332164","op1":"","op2":"Kapı 30 saniyedir açık!!","op1type":"nul","op2type":"str","duration":"30","extend":false,"units":"s","reset":"1","bytopic":"all","name":"","x":390,"y":120,"wires":[[]]},{"id":"90e7351e.a17e68","type":"change","z":"5bc4fcf5.332164","name":"High","rules":[{"t":"set","p":"bc","pt":"msg","to":"orange","tot":"str"},{"t":"set","p":"status","pt":"msg","to":"Kapalı","tot":"str"}],"action":"","property":"","from":"","to":"","reg":false,"x":590,"y":80,"wires":[["26fe58d.da0d2a8"]]},{"id":"54e68c10.45d2f4","type":"change","z":"5bc4fcf5.332164","name":"Low","rules":[{"t":"set","p":"bc","pt":"msg","to":"purple","tot":"str"},{"t":"set","p":"status","pt":"msg","to":"Açık","tot":"str"}],"action":"","property":"","from":"","to":"","reg":false,"x":590,"y":120,"wires":[["26fe58d.da0d2a8"]]},{"id":"26fe58d.da0d2a8","type":"function","z":"5bc4fcf5.332164","name":"","func":"\nreturn msg;","outputs":1,"noerr":0,"x":780,"y":100,"wires":[[]]},{"id":"8665aba4.942bc8","type":"change","z":"f3966c66.61ab9","name":"","rules":[{"t":"set","p":"payload","pt":"msg","to":"","tot":"date"},{"t":"set","p":"topic","pt":"msg","to":"epoch","tot":"str"}],"action":"","property":"","from":"","to":"","reg":false,"x":510,"y":180,"wires":[["7ace719e.dd215"]]},{"id":"c3ed8690.589198","type":"switch","z":"f3966c66.61ab9","name":"","property":"payload","propertyType":"msg","rules":[{"t":"eq","v":"1","vt":"num"},{"t":"eq","v":"0","vt":"num"}],"checkall":"false","repair":false,"outputs":2,"x":340,"y":200,"wires":[["8665aba4.942bc8"],["88fc78b8.fd7988"]]},{"id":"88fc78b8.fd7988","type":"change","z":"f3966c66.61ab9","name":"","rules":[{"t":"set","p":"payload","pt":"msg","to":"","tot":"date"},{"t":"set","p":"topic","pt":"msg","to":"durum","tot":"str"},{"t":"set","p":"complete","pt":"msg","to":"true","tot":"bool"}],"action":"","property":"","from":"","to":"","reg":false,"x":510,"y":220,"wires":[["7ace719e.dd215"]]},{"id":"7ace719e.dd215","type":"join","z":"f3966c66.61ab9","name":"","mode":"custom","build":"object","property":"payload","propertyType":"msg","key":"topic","joiner":"\\n","joinerType":"str","accumulate":false,"timeout":"","count":"","reduceRight":false,"reduceExp":"","reduceInit":"","reduceInitType":"","reduceFixup":"","x":670,"y":200,"wires":[["a26b3aa1.1029c8","ffbc2c4e.3259e"]]},{"id":"a26b3aa1.1029c8","type":"template","z":"f3966c66.61ab9","name":"","field":"topic","fieldType":"msg","format":"handlebars","syntax":"mustache","template":"INSERT INTO 'sensor_durumu' ('durum','epoc') VALUES ({{payload.durum}},{{payload.epoch}});","output":"str","x":850,"y":200,"wires":[["1fa43285.c6713d"]]},{"id":"ffbc2c4e.3259e","type":"function","z":"f3966c66.61ab9","name":"","func":"msg.payload = (msg.payload.durum - msg.payload.epoch)/1000;\nmsg.topic = \"time diff\";\nreturn msg;","outputs":1,"noerr":0,"x":870,"y":120,"wires":[["53fb1968.72f428","b179a5fa.9bd698"]]},{"id":"1fa43285.c6713d","type":"debug","z":"f3966c66.61ab9","name":"store in db","active":false,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","x":1060,"y":200,"wires":[]},{"id":"53fb1968.72f428","type":"ui_chart","z":"f3966c66.61ab9","name":"","group":"7f157da0.9d75f4","order":4,"width":"10","height":"10","label":"mySQL data","chartType":"line","legend":"true","xformat":"HH:mm:ss","interpolate":"bezier","nodata":"","dot":false,"ymin":"","ymax":"","removeOlder":"2","removeOlderPoints":"1000","removeOlderUnit":"3600","cutout":0,"useOneColor":false,"colors":["#1f77b4","#aec7e8","#ff7f0e","#2ca02c","#98df8a","#d62728","#ff9896","#9467bd","#c5b0d5"],"useOldStyle":true,"x":1060,"y":120,"wires":[[]]},{"id":"b179a5fa.9bd698","type":"debug","z":"f3966c66.61ab9","name":"","active":false,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","x":1080,"y":80,"wires":[]},{"id":"ab569222.21cbe","type":"sqlite","z":"f3966c66.61ab9","mydb":"e35ee04b.a8bff","sqlquery":"msg.topic","sql":"","name":"sqliteDB","x":960,"y":420,"wires":[["255d44ec.4fe69c"]]},{"id":"bbc0d33e.3c661","type":"ui_button","z":"f3966c66.61ab9","name":"","group":"c55d32a2.85a79","order":9,"width":"3","height":"1","passthru":false,"label":"B5 Kapı - 1 {{msg.status}}","color":"","bgcolor":"{{bc}}","icon":"","payload":"1","payloadType":"num","topic":"topic","x":590,"y":340,"wires":[[]]},{"id":"decb95c3.0e60b8","type":"template","z":"f3966c66.61ab9","name":"","field":"topic","fieldType":"msg","format":"handlebars","syntax":"mustache","template":"INSERT INTO 'sensor_durumu' ('durum','epoc') VALUES ({{pyload}},{{zaman}});","output":"str","x":800,"y":420,"wires":[["ab569222.21cbe"]]},{"id":"f94f4ab.35715b8","type":"rpi-gpio in","z":"f3966c66.61ab9","name":"","pin":"40","intype":"down","debounce":"25","read":false,"x":130,"y":380,"wires":[["62f47367.f400cc","516c7814.fdeab8"]]},{"id":"62f47367.f400cc","type":"subflow:5bc4fcf5.332164","z":"f3966c66.61ab9","name":"Pin Status Monitoring","x":340,"y":380,"wires":[["bbc0d33e.3c661","c3ed8690.589198"],["9011dffb.52cb5"]]},{"id":"9011dffb.52cb5","type":"ui_toast","z":"f3966c66.61ab9","position":"dialog","displayTime":"","highlight":"","outputs":1,"ok":"OK","cancel":"","topic":"Uyarı","name":"","x":570,"y":380,"wires":[[]]},{"id":"b6b8950.87d4068","type":"function","z":"f3966c66.61ab9","name":"Zamanın Oluşturulması","func":"var d= new Date();\nvar zaman = d.getTime();\nmsg.zaman=zaman;\nreturn msg;","outputs":1,"noerr":0,"x":570,"y":420,"wires":[["decb95c3.0e60b8"]]},{"id":"255d44ec.4fe69c","type":"debug","z":"f3966c66.61ab9","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","x":1110,"y":420,"wires":[]},{"id":"516c7814.fdeab8","type":"dsm","z":"f3966c66.61ab9","name":"sensör_durumu","sm_config":"{\n \"triggerInput\": \"payload\",\n \"currentState\": \"running\",\n \"states\": {\n \"stopped\": {\n \"0\": \"running\"\n },\n \"running\": {\n \"1\": \"stopped\"\n }\n },\n \"methods\": {\n \"onBeforeTransition\": \"msg.pyload = msg.payload.toString();\",\n \"1\": \"sta.fill='green';msg.background='red';msg.label='S';\",\n \"0\": \"sta.fill='red';msg.background='green';msg.label='S';\"\n }\n}","x":330,"y":420,"wires":[["b6b8950.87d4068","decb95c3.0e60b8"]]},{"id":"7f157da0.9d75f4","type":"ui_group","z":"","name":"Home","tab":"6f5ce300.48f7bc","order":10,"disp":true,"width":"10","collapse":false},{"id":"e35ee04b.a8bff","type":"sqlitedb","z":"","db":"/var/www/html/database/kapi","mode":"RWC"},{"id":"c55d32a2.85a79","type":"ui_group","z":"","name":"Orginal","tab":"6f5ce300.48f7bc","order":9,"disp":true,"width":"6","collapse":false},{"id":"6f5ce300.48f7bc","type":"ui_tab","z":"","name":"Mercedes-Benz Türk AŞ Kamyon Fabrikası Hol Kapıları","icon":"dashboard","order":1}]
And after injection it looks like at photo. Is it possible to calculate automatically and showing the screen time difference.
This is my flow photos
This is my dashboard photo.
Not sure what your asking, I gave an example to make you question your process. I do not have all your nodes installed, so difficult to say, as i don't have an working example of your flow.
Dear @E1cid as I said I want to calculate difference between 0 and 1 position. And also show this time on the dashboard, just like for example
27.02.2022 : 117 min
28.02.2022 : 130 min
29.02.2022 : 110 min
Try this
[{"id":"21ccc01f.e14f6","type":"inject","z":"bf9e1e33.030598","name":"","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"1","payloadType":"num","x":210,"y":1140,"wires":[["e86b671f.563508"]]},{"id":"e86b671f.563508","type":"switch","z":"bf9e1e33.030598","name":"","property":"payload","propertyType":"msg","rules":[{"t":"eq","v":"1","vt":"num"},{"t":"eq","v":"0","vt":"num"}],"checkall":"false","repair":false,"outputs":2,"x":320,"y":1200,"wires":[["9e3a33fa.b9cd88"],["d72a34d0.0d12d8"]]},{"id":"9e3a33fa.b9cd88","type":"change","z":"bf9e1e33.030598","name":"","rules":[{"t":"set","p":"payload","pt":"msg","to":"","tot":"date"},{"t":"set","p":"topic","pt":"msg","to":"epoch","tot":"str"}],"action":"","property":"","from":"","to":"","reg":false,"x":490,"y":1180,"wires":[["26cee816.93d928"]]},{"id":"d72a34d0.0d12d8","type":"change","z":"bf9e1e33.030598","name":"","rules":[{"t":"set","p":"payload","pt":"msg","to":"","tot":"date"},{"t":"set","p":"topic","pt":"msg","to":"durum","tot":"str"},{"t":"set","p":"complete","pt":"msg","to":"true","tot":"bool"}],"action":"","property":"","from":"","to":"","reg":false,"x":490,"y":1220,"wires":[["26cee816.93d928"]]},{"id":"766a2757.16fc2","type":"inject","z":"bf9e1e33.030598","name":"","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"0","payloadType":"num","x":210,"y":1240,"wires":[["e86b671f.563508"]]},{"id":"26cee816.93d928","type":"join","z":"bf9e1e33.030598","name":"","mode":"custom","build":"object","property":"payload","propertyType":"msg","key":"topic","joiner":"\\n","joinerType":"str","accumulate":false,"timeout":"","count":"","reduceRight":false,"reduceExp":"","reduceInit":"","reduceInitType":"","reduceFixup":"","x":650,"y":1200,"wires":[["99f0be0b.56c148"]]},{"id":"99f0be0b.56c148","type":"function","z":"bf9e1e33.030598","name":"","func":"msg.payload = (msg.payload.durum - msg.payload.epoch)/1000;\nmsg.topic = \"time diff\";\nmsg.label = \"mySQL data Total \" + msg.payload\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","x":850,"y":1120,"wires":[["8ded8649.e2dec","24e3ccb9.34163c"]]},{"id":"8ded8649.e2dec","type":"ui_chart","z":"bf9e1e33.030598","name":"","group":"91add270.8b6d88","order":4,"width":"18","height":"10","label":"{{msg.label}}","chartType":"line","legend":"true","xformat":"HH:mm:ss","interpolate":"bezier","nodata":"","dot":false,"ymin":"","ymax":"","removeOlder":"2","removeOlderPoints":"1000","removeOlderUnit":"3600","cutout":0,"useOneColor":false,"useUTC":false,"colors":["#1f77b4","#aec7e8","#ff7f0e","#2ca02c","#98df8a","#d62728","#ff9896","#9467bd","#c5b0d5"],"outputs":1,"useDifferentColor":false,"className":"","x":1010,"y":1120,"wires":[[]]},{"id":"24e3ccb9.34163c","type":"debug","z":"bf9e1e33.030598","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","statusVal":"","statusType":"auto","x":1080,"y":1080,"wires":[]},{"id":"91add270.8b6d88","type":"ui_group","name":"Home","tab":"6f5ce300.48f7bc","order":10,"disp":true,"width":"18","collapse":false},{"id":"6f5ce300.48f7bc","type":"ui_tab","name":"Mercedes-Benz Türk AŞ Kamyon Fabrikası Hol Kapıları","icon":"dashboard","order":1}]
Dear @E1cid its almost done thank you for your support.
the only thing I see on the screen latest value, not total value, I showed in picture. Is it possible to show total value ?
As people here have been trying to point out to you, your db needs to be re-thought, on and off times need to be stored on same table row and a duration, then it would be an easy sql query to get a total. As you have it, the sql query is vastly more complicated, I suggest you redisign your db storage. I have no sqlite db to test queries to get the sum of the on duration. Maybe someone else can help or ask on a sql forum.
Copyright OpenJS Foundation and Node-RED contributors. All rights reserved. The OpenJS Foundation has registered trademarks and uses trademarks. For a list of trademarks of the OpenJS Foundation, please see our Trademark Policy and Trademark List. Trademarks and logos not indicated on the list of OpenJS Foundation trademarks are trademarks™ or registered® trademarks of their respective holders. Use of them does not imply any affiliation with or endorsement by them.
The OpenJS Foundation | Terms of Use | Privacy Policy | OpenJS Foundation Bylaws | Trademark Policy | Trademark List | Cookie Policy
