Data getting from SQLite and filtering daily

[wtrk]

Hello everybody.
I have done my flow here with helps.
I need to take db actions, showing with date and time picker. Im storing data in to the db and just want to see this data (example : Pin high status) .
Thanks for your helps

How can I take data using data and time picker with daily ?

And here is my 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":"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  &nbsp; &nbsp;\" + 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  &nbsp; &nbsp;\" + 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"}]

[E1cid]

Here is an example of querying db for selected date and displaying.

[{"id":"ab4b1268.73f67","type":"ui_date_picker","z":"bf9e1e33.030598","name":"","label":"date","group":"187a0d10.a44d4b","order":25,"width":0,"height":0,"passthru":true,"topic":"topic","topicType":"msg","className":"","x":210,"y":1640,"wires":[["b7944b5f.f20588"]]},{"id":"b7944b5f.f20588","type":"change","z":"bf9e1e33.030598","name":"","rules":[{"t":"set","p":"payload","pt":"msg","to":"$moment($$.payload).format(\"yyyy-MM-DD\")","tot":"jsonata"}],"action":"","property":"","from":"","to":"","reg":false,"x":420,"y":1640,"wires":[["47344f91.e5acf"]]},{"id":"47344f91.e5acf","type":"template","z":"bf9e1e33.030598","name":"","field":"topic","fieldType":"msg","format":"handlebars","syntax":"mustache","template":"SELECT\n    *\nFROM\n    sensor_durumu\nWHERE\n    date = '{{payload}}'\n","output":"str","x":450,"y":1680,"wires":[["73e0f974.4e52a"]]},{"id":"73e0f974.4e52a","type":"function","z":"bf9e1e33.030598","name":"simulate sql query","func":"msg.payload =[\n    {date: msg.payload, ontime: 111111111, offtime: 111112112, duration: 1},\n        {date: msg.payload, ontime: 11117111, offtime: 11118114, duration: 1},\n    {date: msg.payload, ontime: 11118111, offtime: 11120111, duration: 2},\n\n    ]\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","x":540,"y":1720,"wires":[["2c3c1962.3532d6","16dbf96d.a2b33f"]],"icon":"node-red/watch.svg"},{"id":"2c3c1962.3532d6","type":"ui_template","z":"bf9e1e33.030598","group":"187a0d10.a44d4b","name":"","order":3,"width":0,"height":0,"format":"<style>\ntable{\n    background:grey;\n    border: 1px;\n    padding: 2px;\n}\nth, td{ \n    text-align: left;\n    border: 1px;\n    padding: 2px;\n}\n\n</style><table>\n    <tr>\n        <th ng-repeat=\"(key, value) in msg.payload[0]\">{{key}}</th>\n    </tr>\n    <tr ng-repeat=\"row in msg.payload\">\n        <td>{{row.date}}</td>\n        <td>{{row.ontime | date : \"HH:mm:ss\"}}</td>\n        <td>{{row.offtime | date : \"HH:mm:ss\"}}</td>\n        <td>{{row.duration | number : 2}}</td>\n    </tr>\n</table>","storeOutMessages":true,"fwdInMessages":true,"resendOnRefresh":true,"templateScope":"local","className":"","x":583.3333129882812,"y":1767,"wires":[[]]},{"id":"16dbf96d.a2b33f","type":"debug","z":"bf9e1e33.030598","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","statusVal":"","statusType":"auto","x":750,"y":1680,"wires":[]},{"id":"187a0d10.a44d4b","type":"ui_group","name":"Group 1","tab":"8abb3dd0.ddf87","order":1,"disp":false,"width":"6","collapse":false,"className":""},{"id":"8abb3dd0.ddf87","type":"ui_tab","name":"tab3","icon":"dashboard","order":8,"disabled":false,"hidden":false}]

[wtrk]

@E1cid

[{"id":"ab4b1268.73f67","type":"ui_date_picker","z":"bf9e1e33.030598","name":"","label":"date","group":"187a0d10.a44d4b","order":25,"width":0,"height":0,"passthru":true,"topic":"topic","topicType":"msg","className":"","x":210,"y":1640,"wires":[["b7944b5f.f20588"]]},{"id":"b7944b5f.f20588","type":"change","z":"bf9e1e33.030598","name":"","rules":[{"t":"set","p":"payload","pt":"msg","to":"$moment($.payload).format(\"yyyy-MM-DD\")","tot":"jsonata"}],"action":"","property":"","from":"","to":"","reg":false,"x":420,"y":1640,"wires":[["47344f91.e5acf"]]},{"id":"47344f91.e5acf","type":"template","z":"bf9e1e33.030598","name":"","field":"topic","fieldType":"msg","format":"handlebars","syntax":"mustache","template":"SELECT\n    *\nFROM\n    sensor_durumu\nWHERE\n    date = '{{payload}}'\n","output":"str","x":450,"y":1680,"wires":[["73e0f974.4e52a"]]},{"id":"73e0f974.4e52a","type":"function","z":"bf9e1e33.030598","name":"simulate sql query","func":"msg.payload =[\n    {date: msg.payload, ontime: 111111111, offtime: 111112112, duration: 1},\n        {date: msg.payload, ontime: 11117111, offtime: 11118114, duration: 1},\n    {date: msg.payload, ontime: 11118111, offtime: 11120111, duration: 2},\n\n    ]\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","x":540,"y":1720,"wires":[["2c3c1962.3532d6","16dbf96d.a2b33f"]],"icon":"node-red/watch.svg"},{"id":"2c3c1962.3532d6","type":"ui_template","z":"bf9e1e33.030598","group":"187a0d10.a44d4b","name":"","order":3,"width":0,"height":0,"format":"<style>\ntable{\n    background:grey;\n    border: 1px;\n    padding: 2px;\n}\nth, td{ \n    text-align: left;\n    border: 1px;\n    padding: 2px;\n}\n\n</style><table>\n    <tr>\n        <th ng-repeat=\"(key, value) in msg.payload[0]\">{{key}}</th>\n    </tr>\n    <tr ng-repeat=\"row in msg.payload\">\n        <td>{{row.date}}</td>\n        <td>{{row.ontime | date : \"HH:mm:ss\"}}</td>\n        <td>{{row.offtime | date : \"HH:mm:ss\"}}</td>\n        <td>{{row.duration | number : 2}}</td>\n    </tr>\n</table>","storeOutMessages":true,"fwdInMessages":true,"resendOnRefresh":true,"templateScope":"local","className":"","x":583.3333129882812,"y":1767,"wires":[[]]},{"id":"16dbf96d.a2b33f","type":"debug","z":"bf9e1e33.030598","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","statusVal":"","statusType":"auto","x":750,"y":1680,"wires":[]},{"id":"187a0d10.a44d4b","type":"ui_group","name":"Group 1","tab":"8abb3dd0.ddf87","order":1,"disp":false,"width":"6","collapse":false,"className":""},{"id":"8abb3dd0.ddf87","type":"ui_tab","name":"tab3","icon":"dashboard","order":8,"disabled":false,"hidden":false}]

Dear @E1cid I copied and run then I choose date its says this

3/7/2022, 5:45:17 PMnode: c033832d.e83e5
msg : string[62]
"Invalid JSONata expression: Attempted to invoke a non-function"

[E1cid]

you are running an old version of node-red, update it.

Or

use this expression in the change node instead.

$fromMillis($$.payload, "[Y0001]-[M01]-[D01]")

[wtrk]

I will try it and then give you Feedback.
Thanks