Dear @E1cid if you define to me I can also install mysql, sqlite is not important for me. I can also redesign my db no problem
I suggest you mess with this as is, until you undersatnd how it functions . Then integrate it with your flow.Ok this is not tested and you need to add your sqlite nodes where you see the function nodes that simulate the db nodes, as i do not have sqlite installed.
the table needs to be
date(text), ontime(numeric), offtime(numeric), duration(numeric)
[{"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":150,"y":1180,"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":"ontime","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":"offtime","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":150,"y":1220,"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":[["42d0a1cf.e2bc7"]]},{"id":"42d0a1cf.e2bc7","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,"initialize":"","finalize":"","x":490,"y":1260,"wires":[["cf99025d.3a3c88"]]},{"id":"cf99025d.3a3c88","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}}'\n","output":"str","x":490,"y":1300,"wires":[["2fc159be.048b46"]]},{"id":"2fc159be.048b46","type":"function","z":"bf9e1e33.030598","name":"simulate sqllite query result","func":"msg.payload = [{\"total\":64}];\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","x":540,"y":1340,"wires":[["99f0be0b.56c148","d4444d66.d2ffe8"]],"icon":"node-red/watch.svg"},{"id":"99f0be0b.56c148","type":"function","z":"bf9e1e33.030598","name":"","func":"msg.total = msg.payload[0].total;\nmsg.payload = (msg.times.offtime - msg.times.ontime)/1000;\nmsg.total += msg.payload\nmsg.title = \"mySQL data Total \" + msg.total\nmsg.topic = \"on duration\";\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","x":790,"y":1340,"wires":[["7f43e056.9f7c3","8ded8649.e2dec","24e3ccb9.34163c"]]},{"id":"d4444d66.d2ffe8","type":"debug","z":"bf9e1e33.030598","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","statusVal":"","statusType":"auto","x":730,"y":1300,"wires":[]},{"id":"7f43e056.9f7c3","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":1400,"wires":[["888ddd79.6a74a8","68327756.097718"]]},{"id":"8ded8649.e2dec","type":"ui_chart","z":"bf9e1e33.030598","name":"","group":"91add270.8b6d88","order":4,"width":"18","height":"10","label":"{{msg.title}}","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":970,"y":1340,"wires":[[]]},{"id":"24e3ccb9.34163c","type":"debug","z":"bf9e1e33.030598","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","statusVal":"","statusType":"auto","x":890,"y":1220,"wires":[]},{"id":"888ddd79.6a74a8","type":"function","z":"bf9e1e33.030598","name":"simulate db node to store the new row","func":"\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","x":1210,"y":1400,"wires":[[]],"icon":"node-red/watch.svg"},{"id":"68327756.097718","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":1140,"y":1480,"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}]
Again not tested with a sqlite db as i do not have one installed., so you might find some glitches.
[edit] not sure off the result from the db select query(first db node), so there might be an error there, if there is please post debug of result from query.
Also added an AS to query to get a friendly alias for the sum. edited flow above.
2 Likes
what does this do?
Dear @smanjunath211 I think space between words brother
Did google not answer that for you.
1 Like
Yes it did, 
So why don't you?
Looking at your flow leads me to believe you are not very experienced with Node-RED. For instance,
- in your subflow you start and end with
functionnodes that contain only areturn msg. That means those nodes do nothing and are unnecessary. - you send data to a
ui-buttonnode but there is nothing connected to it's output. Maybe you plan to use it in the future, but you should indicate that before sharing your flow - you are already computing the elapsed time the door is open, so use that value to store in the database.
- you use the term
mySQLin the graph and naming a node. This is misleading since you are using SQLite and may confuse someone else in the future who has to change the flow. - why do you add a row to the database when the door opens? Why not insert a row into the database and send a value to the graph, when the door closes?
And you still haven't explained what type of graph you want. Do you want a line graph showing the amount of time the door was open or a graph with two lines - one showing the exact time the door was opened and another showing the exact time the door was closed. Or maybe you want to show a bar graph of the elapsed time the door was open, Or maybe you want a countdown timer of the dashboard that starts when the door is opened.
At this point, no one but you knows the answer to that.
And lastly, when someone asks you questions, answer all of them if you want them to spend their time helping you. After all no one is being paid to help you so you shouldn't waste their time.
CANNED REPLY
I recommend watching this playlist: Node-RED Essentials. The videos are done by the developers of node-red. They're nice & short and to the point. You will understand a whole lot more in about 1 hour. A small investment for a lot of gain.
@zenofmud thank you for your explaining. Im greatfull,
Actually some node in my flow can be look unnecessary, because I dont know very well, so just copy /paste and this kind of things Im trying to make/create something.
Basically I can start again, I can usesqlite/mysql also phpliteadmin/phpmyadmin, these are just details. If someone show me or advice me do this way I can do this.
The aim of my expectations.
1- There is a pin Raspberry GPIO21. (completed)
2- This pin has 2 status Low/High (completed)
3-I want to show this pin status high/low with button. (completed)
4-I want to save this pin status on the DB (Completed)
5-I want to calculate how many second pin status is high (picture below) (completed)
6-As you see in picture last high value. this value times always changing. Any time I want get these values from database and show in textbox this value.
for example
01.03.2022 : 138 second
02.03.2022:155 second
Basically I want to save pin high status and time to DB and after 10 days later go back and look how many second pin status was high.
I think I dont know something thatsway I can not explain it clearly. Sorry for my all expectations , I want to do it but trying always.
Dear @zenofmud I missed your first reply sorry for that.
Dear @E1cid thanks for the all helps. It works fine only one exception. I created db and saving new values. But the date always showing 2018 and also when I change pin status total value going forward and backward. You can see in gif photo.
The device your running on must not have the correct date set. I don't know what you expect me to do, i can not diagnose errors from an image, I would need to see the flow and the information from debugs.
Dear @E1cid there are 3 debug node and Im adding inside the debug.
3/2/2022, 9:06:32 AMnode: de3e88a3.845d48
açık durumu (sn) : msg : Object
object
topic: "açık durumu (sn)"
payload: 2.948
_msgid: "8f24c5f8.434708"
bc: "orange"
status: "High"
times: object
ontime: 1646201190769
offtime: 1646201193717
date: "2022-03-02"
total: 66.948
title: "Pin High Status Total : 66.948"
3/2/2022, 9:06:32 AMnode: d6c0f7f6.2e2508
SELECT sum(duration) AS total FROM sensor_durumu WHERE date = '2022-03-02' : msg.payload : array[1]
array[1]
0: object
total: 64
3/2/2022, 9:06:32 AMnode: store in db
INSERT INTO sensor_durumu (date, ontime, offtime , duration ) VALUES (2022-03-02, 1646201190769, 1646201193717, 2.948); : msg.payload : number
2.948
and also mu 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":"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":"24a24453.0f326c","type":"rpi-gpio in","z":"18ba2bbd.bfb2b4","name":"","pin":"40","intype":"down","debounce":"25","read":false,"x":80,"y":200,"wires":[["ff139c0b.e36ae"]]},{"id":"1d6f36a.a6be3c9","type":"switch","z":"18ba2bbd.bfb2b4","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":260,"y":220,"wires":[["e89f92bb.1d0d"],["8f46e8cc.90b418"]]},{"id":"e89f92bb.1d0d","type":"change","z":"18ba2bbd.bfb2b4","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":430,"y":200,"wires":[["584ad012.78b8c"]]},{"id":"8f46e8cc.90b418","type":"change","z":"18ba2bbd.bfb2b4","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":430,"y":240,"wires":[["584ad012.78b8c"]]},{"id":"584ad012.78b8c","type":"join","z":"18ba2bbd.bfb2b4","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":590,"y":220,"wires":[["407d6990.6658e8"]]},{"id":"407d6990.6658e8","type":"function","z":"18ba2bbd.bfb2b4","name":"","func":"msg.times = msg.payload\nmsg.times.date = new Date().toISOString().split(\"T\")[0];\nreturn msg;","outputs":1,"noerr":0,"x":430,"y":280,"wires":[["8e61d72d.1e6398"]]},{"id":"8e61d72d.1e6398","type":"template","z":"18ba2bbd.bfb2b4","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}}'\n","output":"str","x":430,"y":320,"wires":[["6f7dc943.52a3c8"]]},{"id":"6f7dc943.52a3c8","type":"function","z":"18ba2bbd.bfb2b4","name":"simulate sqllite query result","func":"msg.payload = [{\"total\":64}];\nreturn msg;","outputs":1,"noerr":0,"x":480,"y":360,"wires":[["42bf5b48.03ac14","d6c0f7f6.2e2508"]],"icon":"node-red/watch.svg"},{"id":"42bf5b48.03ac14","type":"function","z":"18ba2bbd.bfb2b4","name":"","func":"msg.total = msg.payload[0].total;\nmsg.payload = (msg.times.offtime - msg.times.ontime)/1000;\nmsg.total += msg.payload\nmsg.title = \"Pin High Status Total : \" + msg.total\nmsg.topic = \"açık durumu (sn)\";\nreturn msg;","outputs":1,"noerr":0,"x":730,"y":360,"wires":[["89f4103b.8df8f","de3e88a3.845d48","89fe727d.5d879"]]},{"id":"d6c0f7f6.2e2508","type":"debug","z":"18ba2bbd.bfb2b4","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","x":670,"y":320,"wires":[]},{"id":"89f4103b.8df8f","type":"template","z":"18ba2bbd.bfb2b4","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":870,"y":420,"wires":[["5c87710a.2786a","b126e467.8d9218"]]},{"id":"de3e88a3.845d48","type":"debug","z":"18ba2bbd.bfb2b4","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","x":830,"y":240,"wires":[]},{"id":"5c87710a.2786a","type":"function","z":"18ba2bbd.bfb2b4","name":"simulate db node to store the new row","func":"\nreturn msg;","outputs":1,"noerr":0,"x":1150,"y":420,"wires":[["41141876.cfa218"]],"icon":"node-red/watch.svg"},{"id":"b126e467.8d9218","type":"debug","z":"18ba2bbd.bfb2b4","name":"store in db","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","x":1080,"y":500,"wires":[]},{"id":"ff139c0b.e36ae","type":"subflow:5bc4fcf5.332164","z":"18ba2bbd.bfb2b4","name":"Pin Status Monitoring","x":140,"y":280,"wires":[["91e23c95.15301","1d6f36a.a6be3c9"],["dab16c7.a756e9"]]},{"id":"91e23c95.15301","type":"ui_button","z":"18ba2bbd.bfb2b4","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":230,"y":320,"wires":[[]]},{"id":"dab16c7.a756e9","type":"ui_toast","z":"18ba2bbd.bfb2b4","position":"dialog","displayTime":"","highlight":"","outputs":1,"ok":"OK","cancel":"","topic":"Uyarı","name":"","x":190,"y":360,"wires":[[]]},{"id":"41141876.cfa218","type":"sqlite","z":"18ba2bbd.bfb2b4","mydb":"e35ee04b.a8bff","sqlquery":"msg.topic","sql":"","name":"db ","x":1190,"y":320,"wires":[[]]},{"id":"89fe727d.5d879","type":"ui_text","z":"18ba2bbd.bfb2b4","group":"8af25296.497b5","order":0,"width":"6","height":"2","name":"","label":"{{msg.title}}","format":"{{msg.payload}}","layout":"row-spread","x":920,"y":320,"wires":[]},{"id":"c55d32a2.85a79","type":"ui_group","z":"","name":"Orginal","tab":"6f5ce300.48f7bc","order":9,"disp":true,"width":"6","collapse":false},{"id":"e35ee04b.a8bff","type":"sqlitedb","z":"","db":"/var/www/html/database/kapi","mode":"RWC"},{"id":"8af25296.497b5","type":"ui_group","z":"","name":"Bina 1","tab":"6f5ce300.48f7bc","order":1,"disp":true,"width":"6","collapse":false},{"id":"6f5ce300.48f7bc","type":"ui_tab","z":"","name":"Building Gates","icon":"dashboard","order":1}]
and also my db structure photo
this is phpliteadmin screen
Remove the default value setting from the date field on your table.
You have not added 2 sqlite nodes in place of the simulated ones in the flow.
Can you explain why you reversed the switch node outputs?
1 Like
Hello @E1cid I have added 2 sqlite node in flow.
Is that correct ?
I created again sensor_durumu fields but still recording is only like 2017
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":"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":"1d6f36a.a6be3c9","type":"switch","z":"18ba2bbd.bfb2b4","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":260,"y":220,"wires":[["e89f92bb.1d0d"],["8f46e8cc.90b418"]]},{"id":"e89f92bb.1d0d","type":"change","z":"18ba2bbd.bfb2b4","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":430,"y":200,"wires":[["584ad012.78b8c"]]},{"id":"8f46e8cc.90b418","type":"change","z":"18ba2bbd.bfb2b4","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":430,"y":240,"wires":[["584ad012.78b8c"]]},{"id":"584ad012.78b8c","type":"join","z":"18ba2bbd.bfb2b4","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":590,"y":220,"wires":[["407d6990.6658e8"]]},{"id":"407d6990.6658e8","type":"function","z":"18ba2bbd.bfb2b4","name":"","func":"msg.times = msg.payload\nmsg.times.date = new Date().toISOString().split(\"T\")[0];\nreturn msg;","outputs":1,"noerr":0,"x":430,"y":280,"wires":[["8e61d72d.1e6398"]]},{"id":"8e61d72d.1e6398","type":"template","z":"18ba2bbd.bfb2b4","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}}'\n","output":"str","x":430,"y":320,"wires":[["6f7dc943.52a3c8"]]},{"id":"6f7dc943.52a3c8","type":"function","z":"18ba2bbd.bfb2b4","name":"simulate sqllite query result","func":"msg.payload = [{\"total\":64}];\nreturn msg;","outputs":1,"noerr":0,"x":480,"y":360,"wires":[["42bf5b48.03ac14","d6c0f7f6.2e2508","c467e242.c31ab"]],"icon":"node-red/watch.svg"},{"id":"42bf5b48.03ac14","type":"function","z":"18ba2bbd.bfb2b4","name":"","func":"msg.total = msg.payload[0].total;\nmsg.payload = (msg.times.offtime - msg.times.ontime)/1000;\nmsg.total += msg.payload\nmsg.title = \"Pin High Status Total : \" + msg.total\nmsg.topic = \"açık durumu (sn)\";\nreturn msg;","outputs":1,"noerr":0,"x":730,"y":360,"wires":[["89f4103b.8df8f","de3e88a3.845d48","89fe727d.5d879"]]},{"id":"d6c0f7f6.2e2508","type":"debug","z":"18ba2bbd.bfb2b4","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","x":670,"y":320,"wires":[]},{"id":"89f4103b.8df8f","type":"template","z":"18ba2bbd.bfb2b4","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":870,"y":420,"wires":[["5c87710a.2786a","b126e467.8d9218"]]},{"id":"de3e88a3.845d48","type":"debug","z":"18ba2bbd.bfb2b4","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","x":830,"y":240,"wires":[]},{"id":"5c87710a.2786a","type":"function","z":"18ba2bbd.bfb2b4","name":"simulate db node to store the new row","func":"\nreturn msg;","outputs":1,"noerr":0,"x":1150,"y":420,"wires":[["41141876.cfa218"]],"icon":"node-red/watch.svg"},{"id":"b126e467.8d9218","type":"debug","z":"18ba2bbd.bfb2b4","name":"store in db","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","x":1080,"y":500,"wires":[]},{"id":"24a24453.0f326c","type":"rpi-gpio in","z":"18ba2bbd.bfb2b4","name":"","pin":"40","intype":"down","debounce":"25","read":false,"x":80,"y":200,"wires":[["ff139c0b.e36ae"]]},{"id":"ff139c0b.e36ae","type":"subflow:5bc4fcf5.332164","z":"18ba2bbd.bfb2b4","name":"Pin Status Monitoring","x":140,"y":280,"wires":[["91e23c95.15301","1d6f36a.a6be3c9"],["dab16c7.a756e9"]]},{"id":"91e23c95.15301","type":"ui_button","z":"18ba2bbd.bfb2b4","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":230,"y":320,"wires":[[]]},{"id":"dab16c7.a756e9","type":"ui_toast","z":"18ba2bbd.bfb2b4","position":"dialog","displayTime":"","highlight":"","outputs":1,"ok":"OK","cancel":"","topic":"Uyarı","name":"","x":190,"y":360,"wires":[[]]},{"id":"41141876.cfa218","type":"sqlite","z":"18ba2bbd.bfb2b4","mydb":"e35ee04b.a8bff","sqlquery":"msg.topic","sql":"","name":"db ","x":1330,"y":480,"wires":[[]]},{"id":"89fe727d.5d879","type":"ui_text","z":"18ba2bbd.bfb2b4","group":"8af25296.497b5","order":0,"width":"6","height":"2","name":"","label":"{{msg.title}}","format":"{{msg.payload}}","layout":"row-spread","x":920,"y":320,"wires":[]},{"id":"c467e242.c31ab","type":"sqlite","z":"18ba2bbd.bfb2b4","mydb":"e35ee04b.a8bff","sqlquery":"msg.topic","sql":"","name":"db ","x":630,"y":460,"wires":[[]]},{"id":"c55d32a2.85a79","type":"ui_group","z":"","name":"Orginal","tab":"6f5ce300.48f7bc","order":9,"disp":true,"width":"6","collapse":false},{"id":"e35ee04b.a8bff","type":"sqlitedb","z":"","db":"/var/www/html/database/kapi","mode":"RWC"},{"id":"8af25296.497b5","type":"ui_group","z":"","name":"Bina 1","tab":"6f5ce300.48f7bc","order":1,"disp":true,"width":"6","collapse":false},{"id":"6f5ce300.48f7bc","type":"ui_tab","z":"","name":"Building Gates","icon":"dashboard","order":1}]
I changed switch node outputs because I have confused high and low status, sorry. Is that important ?
This means you need to replace the simulated with sqlite nodes. Then show us the debugs again.
There is also an error in the second template node, i forgot the quotes around date string
[{"id":"8698b7f1.e3421","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":1060,"y":360,"wires":[["dd56a91.5cea258","359ad969.5ee1e6"]]}]
OK I have changed template node. This is my debug
3/2/2022, 12:05:09 PMnode: de3e88a3.845d48
açık durumu (sn) : msg : Object
object
topic: "açık durumu (sn)"
payload: 16.555
_msgid: "b81ec319.b71a2"
bc: "orange"
status: "High"
times: object
ontime: 1646222717314
offtime: 1646222733869
date: "2022-03-02"
total: 80.555
title: "Pin High Status Total : 80.555"
3/2/2022, 12:05:09 PMnode: d6c0f7f6.2e2508
SELECT sum(duration) AS total FROM sensor_durumu WHERE date = '2022-03-02' : msg.payload : array[1]
array[1]
0: object
3/2/2022, 12:05:09 PMnode: store in db
INSERT INTO sensor_durumu (date, ontime, offtime , duration ) VALUES (2022-03-02, 1646222717314, 1646222733869, 16.555); : msg.payload : number
16.555
And have you put the sqlite nodes in the correct place, and is the db now filling the date field correctly?
from the debugs i can see you have not replaced the second template node
INSERT INTO sensor_durumu (date, ontime, offtime , duration ) VALUES (2022-03-02, 1646222717314, 1646222733869, 16.555);
As no quotes around the string value.
p.s. please use the copy value button in the debug, so we can see all the debug data.
Ok I also added mini video for the problem.
https://www.youtube.com/watch?v=pX03UgmJ3WQ
{"topic":"açık durumu (sn)","payload":9.586,"_msgid":"c7fac26d.450cb","bc":"orange","status":"High","times":{"ontime":1646224294504,"offtime":1646224304090,"date":"2022-03-02"},"total":73.586,"title":"Pin High Status Total : 73.586"}
[{"total":64}]
9.586
{"topic":"açık durumu (sn)","payload":11.909,"_msgid":"4196bb5d.44c474","bc":"orange","status":"High","times":{"ontime":1646224314578,"offtime":1646224326487,"date":"2022-03-02"},"total":75.909,"title":"Pin High Status Total : 75.909"}
[{"total":64}]
11.909
Have you replaced the template node?
what do the sol queries look like?
and show me the flow so i know what you have done.
I can not diagnose things if you do not supply answers.
Please answer the question i ask.Including the previous questions you ignored.
A video does not answer my question, please follow instruction and respond to my queries with answers.
Answer for old message : The db filling correctly but only DD-MM-YY. But time is not showing.Im adding screenshot
Have you replaced the template node? yes changed
what do the sol queries look like? Actually I dont understand this question sorry.
If its SQL query where can I look for this ?
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":"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":"24a24453.0f326c","type":"rpi-gpio in","z":"18ba2bbd.bfb2b4","name":"","pin":"40","intype":"down","debounce":"25","read":false,"x":80,"y":200,"wires":[["ff139c0b.e36ae"]]},{"id":"1d6f36a.a6be3c9","type":"switch","z":"18ba2bbd.bfb2b4","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":260,"y":220,"wires":[["e89f92bb.1d0d"],["8f46e8cc.90b418"]]},{"id":"e89f92bb.1d0d","type":"change","z":"18ba2bbd.bfb2b4","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":430,"y":200,"wires":[["584ad012.78b8c"]]},{"id":"8f46e8cc.90b418","type":"change","z":"18ba2bbd.bfb2b4","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":430,"y":240,"wires":[["584ad012.78b8c"]]},{"id":"584ad012.78b8c","type":"join","z":"18ba2bbd.bfb2b4","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":590,"y":220,"wires":[["407d6990.6658e8"]]},{"id":"407d6990.6658e8","type":"function","z":"18ba2bbd.bfb2b4","name":"","func":"msg.times = msg.payload\nmsg.times.date = new Date().toISOString().split(\"T\")[0];\nreturn msg;","outputs":1,"noerr":0,"x":430,"y":280,"wires":[["8e61d72d.1e6398"]]},{"id":"8e61d72d.1e6398","type":"template","z":"18ba2bbd.bfb2b4","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}}'\n","output":"str","x":430,"y":320,"wires":[["6f7dc943.52a3c8"]]},{"id":"6f7dc943.52a3c8","type":"function","z":"18ba2bbd.bfb2b4","name":"simulate sqllite query result","func":"msg.payload = [{\"total\":64}];\nreturn msg;","outputs":1,"noerr":0,"x":480,"y":360,"wires":[["42bf5b48.03ac14","d6c0f7f6.2e2508","c467e242.c31ab"]],"icon":"node-red/watch.svg"},{"id":"42bf5b48.03ac14","type":"function","z":"18ba2bbd.bfb2b4","name":"","func":"msg.total = msg.payload[0].total;\nmsg.payload = (msg.times.offtime - msg.times.ontime)/1000;\nmsg.total += msg.payload\nmsg.title = \"Pin High Status Total : \" + msg.total\nmsg.topic = \"Pin high (sn)\";\nreturn msg;","outputs":1,"noerr":0,"x":730,"y":360,"wires":[["de3e88a3.845d48","89fe727d.5d879","c1bcb080.c2ad9"]]},{"id":"d6c0f7f6.2e2508","type":"debug","z":"18ba2bbd.bfb2b4","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","x":670,"y":320,"wires":[]},{"id":"de3e88a3.845d48","type":"debug","z":"18ba2bbd.bfb2b4","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","x":830,"y":240,"wires":[]},{"id":"5c87710a.2786a","type":"function","z":"18ba2bbd.bfb2b4","name":"simulate db node to store the new row","func":"\nreturn msg;","outputs":1,"noerr":0,"x":1150,"y":420,"wires":[["41141876.cfa218"]],"icon":"node-red/watch.svg"},{"id":"b126e467.8d9218","type":"debug","z":"18ba2bbd.bfb2b4","name":"store in db","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","x":1080,"y":500,"wires":[]},{"id":"ff139c0b.e36ae","type":"subflow:5bc4fcf5.332164","z":"18ba2bbd.bfb2b4","name":"Pin Status Monitoring","x":140,"y":280,"wires":[["91e23c95.15301","1d6f36a.a6be3c9"],["dab16c7.a756e9"]]},{"id":"91e23c95.15301","type":"ui_button","z":"18ba2bbd.bfb2b4","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":230,"y":320,"wires":[[]]},{"id":"dab16c7.a756e9","type":"ui_toast","z":"18ba2bbd.bfb2b4","position":"dialog","displayTime":"","highlight":"","outputs":1,"ok":"OK","cancel":"","topic":"Uyarı","name":"","x":190,"y":360,"wires":[[]]},{"id":"41141876.cfa218","type":"sqlite","z":"18ba2bbd.bfb2b4","mydb":"e35ee04b.a8bff","sqlquery":"msg.topic","sql":"","name":"db","x":1330,"y":460,"wires":[[]]},{"id":"89fe727d.5d879","type":"ui_text","z":"18ba2bbd.bfb2b4","group":"8af25296.497b5","order":0,"width":"6","height":"2","name":"","label":"{{msg.title}}","format":"","layout":"row-spread","x":920,"y":320,"wires":[]},{"id":"c467e242.c31ab","type":"sqlite","z":"18ba2bbd.bfb2b4","mydb":"e35ee04b.a8bff","sqlquery":"msg.topic","sql":"","name":"db","x":670,"y":420,"wires":[[]]},{"id":"c1bcb080.c2ad9","type":"template","z":"18ba2bbd.bfb2b4","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":880,"y":420,"wires":[["5c87710a.2786a","b126e467.8d9218"]]},{"id":"c55d32a2.85a79","type":"ui_group","z":"","name":"Orginal","tab":"6f5ce300.48f7bc","order":9,"disp":true,"width":"6","collapse":false},{"id":"e35ee04b.a8bff","type":"sqlitedb","z":"","db":"/var/www/html/database/kapi","mode":"RWC"},{"id":"8af25296.497b5","type":"ui_group","z":"","name":"Bina 1","tab":"6f5ce300.48f7bc","order":1,"disp":true,"width":"6","collapse":false},{"id":"6f5ce300.48f7bc","type":"ui_tab","z":"","name":"Building Gates","icon":"dashboard","order":1}]
