The time is not required as the on and off time db entries contain the time, the date is there to give opportunity to group by date simply.
You still have not replaced the simulated db nodes with sqlite nodes.
Replace means delete simulated nodes and put sqlite nodes in there place. Once you get the sqlite nodes in correct place things should work better.
The sql queries are the topics created by the template nodes.
Im so sorry, you are right, I deleted function nodes and put sqlite nodes. Now it works thank you. I miss understand. Thank you so much.
The final question is this ;
How can I get total high pin status data from SQL to txtbox daily ?
The first sqlite node returns the total what does the debug show?
Hello its returning this.
SELECT sum(duration) AS total FROM sensor_durumu WHERE date = '2022-03-02' : msg.payload : array[1]
array[1]
0: object
total: 173.19400000000002
So if you want the running total as well as daily total, you would add a UNION select query, then in the function node that creates the text, add the new data.
example
[{"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","name":"","op1":"","op2":"Kapı 30 saniyedir aƧık!!","op1type":"nul","op2type":"str","duration":"30","extend":false,"units":"s","reset":"1","bytopic":"all","outputs":1,"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":"High","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":"Low","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":"d52360b0.ead3a8","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":150,"y":40,"wires":[["ff139c0b.e36ae"]]},{"id":"ff139c0b.e36ae","type":"subflow:5bc4fcf5.332164","z":"bf9e1e33.030598","name":"Pin Status Monitoring","x":190,"y":160,"wires":[["91e23c95.15301","1d6f36a.a6be3c9"],["dab16c7.a756e9"]]},{"id":"91e23c95.15301","type":"ui_button","z":"bf9e1e33.030598","name":"","group":"c55d32a2.85a79","order":9,"width":"6","height":"2","passthru":false,"label":"B5 Gate - 1 {{msg.status}}","color":"","bgcolor":"{{bc}}","icon":"","payload":"1","payloadType":"num","topic":"topic","x":280,"y":200,"wires":[[]]},{"id":"1d6f36a.a6be3c9","type":"switch","z":"bf9e1e33.030598","name":"","property":"payload","propertyType":"msg","rules":[{"t":"eq","v":"0","vt":"num"},{"t":"eq","v":"1","vt":"num"}],"checkall":"false","repair":false,"outputs":2,"x":310,"y":100,"wires":[["e89f92bb.1d0d"],["8f46e8cc.90b418"]]},{"id":"dab16c7.a756e9","type":"ui_toast","z":"bf9e1e33.030598","position":"dialog","displayTime":"","highlight":"","outputs":1,"ok":"OK","cancel":"","topic":"Uyarı","name":"","x":240,"y":240,"wires":[[]]},{"id":"5934b4d1.c2f46c","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":100,"wires":[["ff139c0b.e36ae"]]},{"id":"e89f92bb.1d0d","type":"change","z":"bf9e1e33.030598","name":"","rules":[{"t":"set","p":"payload","pt":"msg","to":"","tot":"date"},{"t":"set","p":"topic","pt":"msg","to":"ontime","tot":"str"}],"action":"","property":"","from":"","to":"","reg":false,"x":480,"y":80,"wires":[["584ad012.78b8c"]]},{"id":"8f46e8cc.90b418","type":"change","z":"bf9e1e33.030598","name":"","rules":[{"t":"set","p":"payload","pt":"msg","to":"","tot":"date"},{"t":"set","p":"topic","pt":"msg","to":"offtime","tot":"str"},{"t":"set","p":"complete","pt":"msg","to":"true","tot":"bool"}],"action":"","property":"","from":"","to":"","reg":false,"x":480,"y":120,"wires":[["584ad012.78b8c"]]},{"id":"584ad012.78b8c","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":640,"y":100,"wires":[["407d6990.6658e8"]]},{"id":"407d6990.6658e8","type":"function","z":"bf9e1e33.030598","name":"","func":"msg.times = msg.payload\nmsg.times.date = new Date().toISOString().split(\"T\")[0];\nreturn msg;","outputs":1,"noerr":0,"x":480,"y":160,"wires":[["8e61d72d.1e6398"]]},{"id":"8e61d72d.1e6398","type":"template","z":"bf9e1e33.030598","name":"","field":"topic","fieldType":"msg","format":"handlebars","syntax":"mustache","template":"SELECT\n sum(duration) AS total\nFROM\n sensor_durumu\nWHERE\n date = '{{times.date}}'\nUNION\nSELECT\n sum(duration) AS full_total\nFROM\n sensor_durumu\n","output":"str","x":480,"y":200,"wires":[["6f7dc943.52a3c8"]]},{"id":"6f7dc943.52a3c8","type":"function","z":"bf9e1e33.030598","name":"simulate sqllite query result","func":"msg.payload = [{\"total\":64,\"full_total\":700}];\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","x":530,"y":240,"wires":[["42bf5b48.03ac14","d6c0f7f6.2e2508"]],"icon":"node-red/watch.svg"},{"id":"42bf5b48.03ac14","type":"function","z":"bf9e1e33.030598","name":"","func":"msg.total = msg.payload[0].total;\nmsg.full_total = msg.payload[0].full_total;\nmsg.payload = (msg.times.offtime - msg.times.ontime)/1000;\nmsg.total += msg.payload;\nmsg.full_total += msg.payload;\nmsg.title = \"Pin High Status \" + msg.times.date + \" Total : \" + msg.total +\n \"<br> Full Total : \" + msg.full_total;\nmsg.topic = \"Pin high (sn)\";\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","x":780,"y":240,"wires":[["de3e88a3.845d48","89fe727d.5d879","c1bcb080.c2ad9"]]},{"id":"d6c0f7f6.2e2508","type":"debug","z":"bf9e1e33.030598","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","x":720,"y":200,"wires":[]},{"id":"de3e88a3.845d48","type":"debug","z":"bf9e1e33.030598","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","x":880,"y":120,"wires":[]},{"id":"89fe727d.5d879","type":"ui_text","z":"bf9e1e33.030598","group":"8af25296.497b5","order":0,"width":"6","height":"2","name":"","label":"{{msg.title}}","format":"","layout":"row-spread","x":970,"y":200,"wires":[]},{"id":"c1bcb080.c2ad9","type":"template","z":"bf9e1e33.030598","name":"","field":"topic","fieldType":"msg","format":"handlebars","syntax":"mustache","template":"INSERT INTO sensor_durumu (date, ontime, offtime , duration ) VALUES ('{{times.date}}', {{times.ontime}}, {{times.offtime}}, {{payload}});","output":"str","x":930,"y":300,"wires":[["5c87710a.2786a","b126e467.8d9218"]]},{"id":"5c87710a.2786a","type":"function","z":"bf9e1e33.030598","name":"simulate db node to store the new row","func":"\nreturn msg;","outputs":1,"noerr":0,"x":1200,"y":300,"wires":[[]],"icon":"node-red/watch.svg"},{"id":"b126e467.8d9218","type":"debug","z":"bf9e1e33.030598","name":"store in db","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","x":1130,"y":380,"wires":[]},{"id":"c55d32a2.85a79","type":"ui_group","name":"Orginal","tab":"6f5ce300.48f7bc","order":9,"disp":true,"width":"6","collapse":false},{"id":"8af25296.497b5","type":"ui_group","name":"Bina 1","tab":"6f5ce300.48f7bc","order":1,"disp":true,"width":"6","collapse":false},{"id":"6f5ce300.48f7bc","type":"ui_tab","name":"Building Gates","icon":"dashboard","order":1}]
Thank you dear @E1cid .
This means I have to create a field in sensor_durumu and it name should be UNION right ?
3/2/2022, 5:01:11 PMnode: 8049a46c.f36088
SELECT sum(duration) AS total FROM sensor_durumu WHERE date = '2022-03-02' UNION SELECT sum(duration) AS full_total FROM sensor_durumu : msg.payload : array[1]
array[1]
0: object
total: 226.322
no, the UNION just allows us to add another query to the sql query. So you can make multiple queries in one request.
Hm ok but Full Total is looking NaN
You again have not followed my response. The template node is altered to. It looks like you just added the function node.
Again i can not diagnose info you do not give me, best to supply how you implemented it, i.e. the flow, and of course debug info. Your images are pretty but hold no useful info.
I also changed template node and inside the template node is looks like this
SELECT
sum(duration) AS total
FROM
sensor_durumu
WHERE
date = '{{times.date}}'
UNION
SELECT
sum(duration) AS full_total
FROM
sensor_durumu
I'm adding final flow.
[{"id":"5bbd4ef.26768b","type":"subflow","name":"Subflow 1 (2)","info":"","category":"","in":[{"x":60,"y":80,"wires":[{"id":"b68edc19.b03f5"}]}],"out":[{"x":920,"y":100,"wires":[{"id":"d55f04d4.71f788","port":0}]},{"x":540,"y":120,"wires":[{"id":"e7edf8b0.2f0a68","port":0}]}]},{"id":"b68edc19.b03f5","type":"function","z":"5bbd4ef.26768b","name":"","func":"\nreturn msg;","outputs":1,"noerr":0,"x":200,"y":80,"wires":[["e7edf8b0.2f0a68","968076b.1b0e988"]]},{"id":"968076b.1b0e988","type":"switch","z":"5bbd4ef.26768b","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":[["a4875894.611908"],["1bfba8b2.065667"]]},{"id":"e7edf8b0.2f0a68","type":"trigger","z":"5bbd4ef.26768b","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":"a4875894.611908","type":"change","z":"5bbd4ef.26768b","name":"High","rules":[{"t":"set","p":"bc","pt":"msg","to":"orange","tot":"str"},{"t":"set","p":"status","pt":"msg","to":"High","tot":"str"}],"action":"","property":"","from":"","to":"","reg":false,"x":590,"y":80,"wires":[["d55f04d4.71f788"]]},{"id":"1bfba8b2.065667","type":"change","z":"5bbd4ef.26768b","name":"Low","rules":[{"t":"set","p":"bc","pt":"msg","to":"purple","tot":"str"},{"t":"set","p":"status","pt":"msg","to":"Low","tot":"str"}],"action":"","property":"","from":"","to":"","reg":false,"x":590,"y":120,"wires":[["d55f04d4.71f788"]]},{"id":"d55f04d4.71f788","type":"function","z":"5bbd4ef.26768b","name":"","func":"\nreturn msg;","outputs":1,"noerr":0,"x":780,"y":100,"wires":[[]]},{"id":"e7660215.f0237","type":"rpi-gpio in","z":"af9b4ff8.78eea","name":"","pin":"40","intype":"down","debounce":"25","read":false,"x":90,"y":140,"wires":[["5d4c11c1.9af9a"]]},{"id":"5d4c11c1.9af9a","type":"subflow:5bbd4ef.26768b","z":"af9b4ff8.78eea","name":"Pin Status Monitoring","x":190,"y":220,"wires":[["173f6245.20c12e","6cb5364e.200ca8"],["5c665a50.7c00d4"]]},{"id":"173f6245.20c12e","type":"ui_button","z":"af9b4ff8.78eea","name":"","group":"c55d32a2.85a79","order":9,"width":"6","height":"2","passthru":false,"label":"B5 Gate - 1 {{msg.status}}","color":"","bgcolor":"{{bc}}","icon":"","payload":"1","payloadType":"num","topic":"topic","x":280,"y":260,"wires":[[]]},{"id":"6cb5364e.200ca8","type":"switch","z":"af9b4ff8.78eea","name":"","property":"payload","propertyType":"msg","rules":[{"t":"eq","v":"0","vt":"num"},{"t":"eq","v":"1","vt":"num"}],"checkall":"false","repair":false,"outputs":2,"x":310,"y":160,"wires":[["2b2539eb.875a76"],["11ed0772.92c5c9"]]},{"id":"5c665a50.7c00d4","type":"ui_toast","z":"af9b4ff8.78eea","position":"dialog","displayTime":"","highlight":"","outputs":1,"ok":"OK","cancel":"","topic":"Uyarı","name":"","x":240,"y":300,"wires":[[]]},{"id":"2b2539eb.875a76","type":"change","z":"af9b4ff8.78eea","name":"","rules":[{"t":"set","p":"payload","pt":"msg","to":"","tot":"date"},{"t":"set","p":"topic","pt":"msg","to":"ontime","tot":"str"}],"action":"","property":"","from":"","to":"","reg":false,"x":480,"y":140,"wires":[["852ddb80.46c328"]]},{"id":"11ed0772.92c5c9","type":"change","z":"af9b4ff8.78eea","name":"","rules":[{"t":"set","p":"payload","pt":"msg","to":"","tot":"date"},{"t":"set","p":"topic","pt":"msg","to":"offtime","tot":"str"},{"t":"set","p":"complete","pt":"msg","to":"true","tot":"bool"}],"action":"","property":"","from":"","to":"","reg":false,"x":480,"y":180,"wires":[["852ddb80.46c328"]]},{"id":"852ddb80.46c328","type":"join","z":"af9b4ff8.78eea","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":640,"y":160,"wires":[["1ec31f14.e84f91"]]},{"id":"1ec31f14.e84f91","type":"function","z":"af9b4ff8.78eea","name":"","func":"msg.times = msg.payload\nmsg.times.date = new Date().toISOString().split(\"T\")[0];\nreturn msg;","outputs":1,"noerr":0,"x":480,"y":220,"wires":[["2fc07d69.2e2962"]]},{"id":"2fc07d69.2e2962","type":"template","z":"af9b4ff8.78eea","name":"","field":"topic","fieldType":"msg","format":"handlebars","syntax":"mustache","template":"SELECT\n sum(duration) AS total\nFROM\n sensor_durumu\nWHERE\n date = '{{times.date}}'\nUNION\nSELECT\n sum(duration) AS full_total\nFROM\n sensor_durumu\n","output":"str","x":480,"y":260,"wires":[["3a0531de.5aa56e"]]},{"id":"3c081f34.24038","type":"function","z":"af9b4ff8.78eea","name":"","func":"msg.total = msg.payload[0].total;\nmsg.full_total = msg.payload[0].full_total;\nmsg.payload = (msg.times.offtime - msg.times.ontime)/1000;\nmsg.total += msg.payload;\nmsg.full_total += msg.payload;\nmsg.title = \"Pin High Status \" + msg.times.date + \" Total : \" + msg.total +\n \"<br> Full Total : \" + msg.full_total;\nmsg.topic = \"Pin high (sn)\";\nreturn msg;","outputs":1,"noerr":0,"x":780,"y":300,"wires":[["cdd056e3.559718","5317643d.ca492c","d994b88e.cdf588"]]},{"id":"8049a46c.f36088","type":"debug","z":"af9b4ff8.78eea","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","x":720,"y":260,"wires":[]},{"id":"cdd056e3.559718","type":"debug","z":"af9b4ff8.78eea","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","x":880,"y":180,"wires":[]},{"id":"5317643d.ca492c","type":"ui_text","z":"af9b4ff8.78eea","group":"c55d32a2.85a79","order":0,"width":"6","height":"2","name":"","label":"{{msg.title}}","format":"","layout":"row-spread","x":970,"y":260,"wires":[]},{"id":"d994b88e.cdf588","type":"template","z":"af9b4ff8.78eea","name":"","field":"topic","fieldType":"msg","format":"handlebars","syntax":"mustache","template":"INSERT INTO sensor_durumu (date, ontime, offtime , duration ) VALUES ('{{times.date}}', {{times.ontime}}, {{times.offtime}}, {{payload}});","output":"str","x":930,"y":360,"wires":[["139c1f1b.ca6d71","53d507d7.5bab88"]]},{"id":"139c1f1b.ca6d71","type":"debug","z":"af9b4ff8.78eea","name":"store in db","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","x":1130,"y":440,"wires":[]},{"id":"3a0531de.5aa56e","type":"sqlite","z":"af9b4ff8.78eea","mydb":"e35ee04b.a8bff","sqlquery":"msg.topic","sql":"","name":"db","x":590,"y":300,"wires":[["3c081f34.24038","8049a46c.f36088"]]},{"id":"53d507d7.5bab88","type":"sqlite","z":"af9b4ff8.78eea","mydb":"e35ee04b.a8bff","sqlquery":"msg.topic","sql":"","name":"db","x":1200,"y":360,"wires":[[]]},{"id":"c55d32a2.85a79","type":"ui_group","z":"","name":"Orginal","tab":"4ad3d549.6d056c","order":9,"disp":true,"width":"6","collapse":false},{"id":"e35ee04b.a8bff","type":"sqlitedb","z":"","db":"/var/www/html/database/kapi","mode":"RWC"},{"id":"4ad3d549.6d056c","type":"ui_tab","z":"","name":"Deneme","icon":"dashboard"}]
Dear @E1cid I need one latest help. Just please can you show me how can I take data from db and show daily.
Its solved. Im greatfull for your all helps, especially @E1cid .
Im sharing here my flow here, if someone help this kind of help, hope it will be good solutions.
[{"id":"5bbd4ef.26768b","type":"subflow","name":"Subflow 1 (2)","info":"","category":"","in":[{"x":60,"y":80,"wires":[{"id":"b68edc19.b03f5"}]}],"out":[{"x":920,"y":100,"wires":[{"id":"d55f04d4.71f788","port":0}]},{"x":540,"y":120,"wires":[{"id":"e7edf8b0.2f0a68","port":0}]}]},{"id":"b68edc19.b03f5","type":"function","z":"5bbd4ef.26768b","name":"","func":"\nreturn msg;","outputs":1,"noerr":0,"x":200,"y":80,"wires":[["e7edf8b0.2f0a68","968076b.1b0e988"]]},{"id":"968076b.1b0e988","type":"switch","z":"5bbd4ef.26768b","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":[["a4875894.611908"],["1bfba8b2.065667"]]},{"id":"e7edf8b0.2f0a68","type":"trigger","z":"5bbd4ef.26768b","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":"a4875894.611908","type":"change","z":"5bbd4ef.26768b","name":"High","rules":[{"t":"set","p":"bc","pt":"msg","to":"orange","tot":"str"},{"t":"set","p":"status","pt":"msg","to":"High","tot":"str"}],"action":"","property":"","from":"","to":"","reg":false,"x":590,"y":80,"wires":[["d55f04d4.71f788"]]},{"id":"1bfba8b2.065667","type":"change","z":"5bbd4ef.26768b","name":"Low","rules":[{"t":"set","p":"bc","pt":"msg","to":"purple","tot":"str"},{"t":"set","p":"status","pt":"msg","to":"Low","tot":"str"}],"action":"","property":"","from":"","to":"","reg":false,"x":590,"y":120,"wires":[["d55f04d4.71f788"]]},{"id":"d55f04d4.71f788","type":"function","z":"5bbd4ef.26768b","name":"","func":"\nreturn msg;","outputs":1,"noerr":0,"x":780,"y":100,"wires":[[]]},{"id":"5d4c11c1.9af9a","type":"subflow:5bbd4ef.26768b","z":"af9b4ff8.78eea","name":"Pin Status Monitoring","x":190,"y":220,"wires":[["173f6245.20c12e","6cb5364e.200ca8"],["5c665a50.7c00d4"]]},{"id":"173f6245.20c12e","type":"ui_button","z":"af9b4ff8.78eea","name":"","group":"c55d32a2.85a79","order":9,"width":"6","height":"2","passthru":false,"label":"B5 Gate - 1 {{msg.status}}","color":"","bgcolor":"{{bc}}","icon":"","payload":"1","payloadType":"num","topic":"topic","x":280,"y":260,"wires":[[]]},{"id":"6cb5364e.200ca8","type":"switch","z":"af9b4ff8.78eea","name":"","property":"payload","propertyType":"msg","rules":[{"t":"eq","v":"0","vt":"num"},{"t":"eq","v":"1","vt":"num"}],"checkall":"false","repair":false,"outputs":2,"x":310,"y":160,"wires":[["2b2539eb.875a76"],["11ed0772.92c5c9"]]},{"id":"5c665a50.7c00d4","type":"ui_toast","z":"af9b4ff8.78eea","position":"dialog","displayTime":"","highlight":"","outputs":1,"ok":"OK","cancel":"","topic":"Uyarı","name":"","x":240,"y":300,"wires":[[]]},{"id":"2b2539eb.875a76","type":"change","z":"af9b4ff8.78eea","name":"","rules":[{"t":"set","p":"payload","pt":"msg","to":"","tot":"date"},{"t":"set","p":"topic","pt":"msg","to":"ontime","tot":"str"}],"action":"","property":"","from":"","to":"","reg":false,"x":480,"y":140,"wires":[["852ddb80.46c328"]]},{"id":"11ed0772.92c5c9","type":"change","z":"af9b4ff8.78eea","name":"","rules":[{"t":"set","p":"payload","pt":"msg","to":"","tot":"date"},{"t":"set","p":"topic","pt":"msg","to":"offtime","tot":"str"},{"t":"set","p":"complete","pt":"msg","to":"true","tot":"bool"}],"action":"","property":"","from":"","to":"","reg":false,"x":480,"y":180,"wires":[["852ddb80.46c328"]]},{"id":"852ddb80.46c328","type":"join","z":"af9b4ff8.78eea","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":640,"y":160,"wires":[["1ec31f14.e84f91"]]},{"id":"1ec31f14.e84f91","type":"function","z":"af9b4ff8.78eea","name":"","func":"msg.times = msg.payload\nmsg.times.date = new Date().toISOString().split(\"T\")[0];\nreturn msg;","outputs":1,"noerr":0,"x":480,"y":220,"wires":[["2fc07d69.2e2962"]]},{"id":"2fc07d69.2e2962","type":"template","z":"af9b4ff8.78eea","name":"","field":"topic","fieldType":"msg","format":"handlebars","syntax":"mustache","template":"SELECT\n sum(duration) AS total\nFROM\n sensor_durumu\nWHERE\n date = '{{times.date}}'\nUNION\nSELECT\n sum(duration) AS full_total\nFROM\n sensor_durumu\n","output":"str","x":480,"y":260,"wires":[["3a0531de.5aa56e"]]},{"id":"3c081f34.24038","type":"function","z":"af9b4ff8.78eea","name":"","func":"//msg.total = msg.payload[0].total;\n//msg.full_total = msg.payload[0].full_total;\n//msg.payload = (msg.times.offtime - msg.times.ontime)/1000;\n//msg.total += msg.payload;\n//msg.full_total += msg.payload;\n//msg.title = \"Pin High Status \" + msg.times.date + \" Total : \" + msg.total +\n// \"<br> Full Total : \" + msg.full_total;\n//msg.topic = \"Pin high (sn)\";\n//return msg;\n\n\n\n\n\nmsg.total = msg.payload[0].total;\nmsg.full_total = msg.payload[1].total;\nmsg.payload = (msg.times.offtime - msg.times.ontime)/1000;\nmsg.total += msg.payload;\nmsg.full_total += msg.payload;\nmsg.title = \"Pin High Status \" + msg.times.date + \" Total : \" + msg.total +\n \"<br> Full Total : \" + msg.full_total;\nmsg.topic = \"Pin high (sn)\";\nreturn msg;","outputs":1,"noerr":0,"x":780,"y":300,"wires":[["cdd056e3.559718","5317643d.ca492c","d994b88e.cdf588"]]},{"id":"8049a46c.f36088","type":"debug","z":"af9b4ff8.78eea","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","x":720,"y":260,"wires":[]},{"id":"cdd056e3.559718","type":"debug","z":"af9b4ff8.78eea","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","x":880,"y":180,"wires":[]},{"id":"5317643d.ca492c","type":"ui_text","z":"af9b4ff8.78eea","group":"c55d32a2.85a79","order":0,"width":"6","height":"2","name":"","label":"{{msg.title}}","format":"","layout":"row-spread","x":970,"y":260,"wires":[]},{"id":"d994b88e.cdf588","type":"template","z":"af9b4ff8.78eea","name":"","field":"topic","fieldType":"msg","format":"handlebars","syntax":"mustache","template":"INSERT INTO sensor_durumu (date, ontime, offtime , duration ) VALUES ('{{times.date}}', {{times.ontime}}, {{times.offtime}}, {{payload}});","output":"str","x":930,"y":360,"wires":[["139c1f1b.ca6d71","53d507d7.5bab88"]]},{"id":"139c1f1b.ca6d71","type":"debug","z":"af9b4ff8.78eea","name":"store in db","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","x":1130,"y":440,"wires":[]},{"id":"3a0531de.5aa56e","type":"sqlite","z":"af9b4ff8.78eea","mydb":"e35ee04b.a8bff","sqlquery":"msg.topic","sql":"","name":"db","x":590,"y":300,"wires":[["3c081f34.24038","8049a46c.f36088"]]},{"id":"53d507d7.5bab88","type":"sqlite","z":"af9b4ff8.78eea","mydb":"e35ee04b.a8bff","sqlquery":"msg.topic","sql":"","name":"db","x":1200,"y":360,"wires":[["3c2eacd.b27fd54"]]},{"id":"e7660215.f0237","type":"rpi-gpio in","z":"af9b4ff8.78eea","name":"","pin":"40","intype":"down","debounce":"25","read":false,"x":90,"y":140,"wires":[["5d4c11c1.9af9a"]]},{"id":"3c2eacd.b27fd54","type":"debug","z":"af9b4ff8.78eea","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","x":1260,"y":260,"wires":[]},{"id":"c55d32a2.85a79","type":"ui_group","z":"","name":"Orginal","tab":"4ad3d549.6d056c","order":9,"disp":true,"width":"6","collapse":false},{"id":"e35ee04b.a8bff","type":"sqlitedb","z":"","db":"/var/www/html/database/kapi","mode":"RWC"},{"id":"4ad3d549.6d056c","type":"ui_tab","z":"","name":"Deneme","icon":"dashboard"}]
This topic was automatically closed 14 days after the last reply. New replies are no longer allowed.
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