How to combine date and time picker get data mysql

General
1

Hello Guys
I Need help, i have promblem generate csv file get mysql
this my flow:

image
image928×478 56.3 KB

and this my function combine:
image
image906×257 15 KB

This my dashboard grab:
image
This my format datetime on mysql
image

Thanks All

2

please supply copyable data. A copy of the context date and time data stored. And the function node. No one can copy and paste an imge.

4

okay thanks u for respon, this my flow node

[{"id":"fced8960.3302d8","type":"ui_date_picker","z":"1960dc1.e397c24","name":"","label":"Start Date","group":"1435e071.39b76","order":1,"width":6,"height":2,"passthru":true,"topic":"payload","topicType":"msg","x":800,"y":700,"wires":[["db743ddf.1cdf2"]]},{"id":"bb241d.419f3be","type":"ui_date_picker","z":"1960dc1.e397c24","name":"","label":"End Date","group":"1435e071.39b76","order":4,"width":6,"height":2,"passthru":true,"topic":"end_date","topicType":"msg","x":700,"y":800,"wires":[["29858d7e.14d6c2"]]},{"id":"73943720.123458","type":"function","z":"1960dc1.e397c24","name":"My SQL Database Request","func":"if(msg.topic == \"download\"){\n    //var port= \"dewa_10\"; //replace with port no\n    var startDate = flow.get(\"startDate\");\n    var startTime = flow.get(\"startTime\");\n    var onDate = startDate + \" \" + startTime;\n    \n    var endDate = flow.get(\"endDate\");\n    var endTime = flow.get(\"endTime\");\n    var offDate = endDate + \" \" + endTime;\n\n    // check if date range is correct\n    if (new Date(endDate) > new Date(startDate)) {\n        msg.topic = \"SELECT * FROM fuzzy WHERE datetime BETWEEN '\" + startDate + \"' AND '\" + endDate + \"' order BY datetime\";\n        return msg;\n    } else {\n        node.warn(\"end date is before start date.\");\n    }\n\n}","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":1160,"y":1200,"wires":[[]]},{"id":"b0a6b2d.9820c5","type":"csv","z":"1960dc1.e397c24","name":"","sep":",","hdrin":true,"hdrout":"none","multi":"one","ret":"\\n","temp":"waktu,L1_THDV,L2_THDV,L3_THDV,L1_THDI, L2_THDI,L3_THDI,L1_L2_VOLTAGE,L2_L3_VOLTAGE,L3_L1_VOLTAGE,L1_N_VOLTAGE,L2_N_VOLTAGE,L3_N_VOLTAGE,L1_CURRENT,L2_CURRENT,L3_CURRENT,L1_MAX_CURRENT,L2_MAX_CURRENT,L3_MAX_CURRENT,SYSTEM_FREQUENCY, AverageVoltageLL,Max_System_PF,L1_tdd,L2_tdd,L3_tdd,status","skip":"1","strings":false,"include_empty_strings":false,"include_null_values":false,"x":1230,"y":980,"wires":[["7847ddfd.282c64"]]},{"id":"d7a49664.ea6c88","type":"http response","z":"1960dc1.e397c24","name":"","statusCode":"","headers":{},"x":1550,"y":980,"wires":[]},{"id":"f130565b.4c5a88","type":"catch","z":"1960dc1.e397c24","name":"","scope":null,"uncaught":false,"x":780,"y":1040,"wires":[["2da78617.39032a","119f9d9a.fea782"]]},{"id":"2da78617.39032a","type":"function","z":"1960dc1.e397c24","name":"Set 404","func":"msg.payload = msg.error;\nmsg.statusCode = 404;//resource not found\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","x":1380,"y":1040,"wires":[["d7a49664.ea6c88"]]},{"id":"119f9d9a.fea782","type":"debug","z":"1960dc1.e397c24","name":"","active":false,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","x":810,"y":1100,"wires":[]},{"id":"c9c865ec.16f888","type":"ui_template","z":"1960dc1.e397c24","group":"1435e071.39b76","name":"ui_temlplate - present download link on dashboard","order":8,"width":10,"height":1,"format":"<head>\n<style>\n.button {\n  background-color: #4CAF50;\n  border: none;\n  color: white;\n  padding: 15px 32px;\n  text-align: center;\n  text-decoration: none;\n  display: inline-block;\n  font-size: 16px;\n}\n</style>\n</head>\n<body>\n<a href=\"/download/output.csv\" download=\"Download CSV\" class=\"button\">Download CSV</a>\n</body>\n<!--div>\n    <a href=\"/data\">CLICK TO DOWNLOAD</a>\n</div-->","storeOutMessages":true,"fwdInMessages":true,"resendOnRefresh":false,"templateScope":"local","x":1130,"y":1080,"wires":[[]]},{"id":"4fdfc12f.c0cac","type":"http in","z":"1960dc1.e397c24","name":"","url":"/download/output.csv","method":"get","upload":false,"swaggerDoc":"","x":710,"y":1200,"wires":[["469a524.1d703ac"]]},{"id":"7847ddfd.282c64","type":"function","z":"1960dc1.e397c24","name":"Set Headers","func":"msg.headers = {}\nmsg.headers[\"Content-type\"] = \"text/csv\";\nmsg.headers[\"Content-Disposition\"] = \"attachment; filename=data.csv\";\nmsg.headers[\"Pragma\"] = \"no-cache\";\nmsg.headers[\"Expires\"] = \"0\";\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","x":1390,"y":980,"wires":[["d7a49664.ea6c88"]]},{"id":"3f503323.02092c","type":"mysql","z":"1960dc1.e397c24","mydb":"e882f2c3.e1f7","name":"DB","x":1030,"y":980,"wires":[["b0a6b2d.9820c5","65aaa7e8.442dd8"]]},{"id":"c3ea697d.895368","type":"ui_text_input","z":"1960dc1.e397c24","name":"","label":"Start Time","tooltip":"","group":"1435e071.39b76","order":2,"width":6,"height":2,"passthru":true,"mode":"time","delay":"0","topic":"topic","topicType":"msg","x":810,"y":740,"wires":[["352040e.0355dc"]]},{"id":"db743ddf.1cdf2","type":"moment","z":"1960dc1.e397c24","name":"","topic":"","input":"payload","inputType":"msg","inTz":"Asia/Bangkok","adjAmount":"7","adjType":"hours","adjDir":"subtract","format":"YYYY-MM-DD","locale":"en_GB","output":"payload","outputType":"msg","outTz":"Asia/Bangkok","x":1040,"y":700,"wires":[["4c6ed513.afa47c"]]},{"id":"4c6ed513.afa47c","type":"change","z":"1960dc1.e397c24","name":"","rules":[{"t":"set","p":"startDate","pt":"flow","to":"payload","tot":"msg"}],"action":"","property":"","from":"","to":"","reg":false,"x":1290,"y":700,"wires":[["c47ca5b2.5197c8"]]},{"id":"29858d7e.14d6c2","type":"moment","z":"1960dc1.e397c24","name":"","topic":"","input":"payload","inputType":"msg","inTz":"Asia/Bangkok","adjAmount":"7","adjType":"hours","adjDir":"subtract","format":"YYYY-MM-DD","locale":"en_GB","output":"payload","outputType":"msg","outTz":"Asia/Bangkok","x":920,"y":800,"wires":[["97ed9b53.d5ca38"]]},{"id":"97ed9b53.d5ca38","type":"change","z":"1960dc1.e397c24","name":"","rules":[{"t":"set","p":"endDate","pt":"flow","to":"payload","tot":"msg"}],"action":"","property":"","from":"","to":"","reg":false,"x":1190,"y":800,"wires":[["c47ca5b2.5197c8"]]},{"id":"352040e.0355dc","type":"moment","z":"1960dc1.e397c24","name":"","topic":"","input":"payload","inputType":"msg","inTz":"Asia/Bangkok","adjAmount":"7","adjType":"hours","adjDir":"subtract","format":"HH:mm:ss","locale":"en_GB","output":"payload","outputType":"msg","outTz":"Asia/Bangkok","x":1060,"y":740,"wires":[["82f239d2.5c12d8"]]},{"id":"82f239d2.5c12d8","type":"change","z":"1960dc1.e397c24","name":"","rules":[{"t":"set","p":"startTime","pt":"flow","to":"payload","tot":"msg"}],"action":"","property":"","from":"","to":"","reg":false,"x":1290,"y":740,"wires":[["c47ca5b2.5197c8"]]},{"id":"53fd7840.d64688","type":"moment","z":"1960dc1.e397c24","name":"","topic":"","input":"payload","inputType":"msg","inTz":"Asia/Bangkok","adjAmount":"7","adjType":"hours","adjDir":"subtract","format":"HH:mm:ss","locale":"en_GB","output":"payload","outputType":"msg","outTz":"Asia/Bangkok","x":920,"y":840,"wires":[["2dbab6a0.961aaa"]]},{"id":"2dbab6a0.961aaa","type":"change","z":"1960dc1.e397c24","name":"","rules":[{"t":"set","p":"endTime","pt":"flow","to":"payload","tot":"msg"}],"action":"","property":"","from":"","to":"","reg":false,"x":1190,"y":840,"wires":[["c47ca5b2.5197c8"]]},{"id":"65aaa7e8.442dd8","type":"debug","z":"1960dc1.e397c24","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","statusVal":"","statusType":"auto","x":1150,"y":920,"wires":[]},{"id":"469a524.1d703ac","type":"change","z":"1960dc1.e397c24","name":"","rules":[{"t":"set","p":"topic","pt":"msg","to":"download","tot":"str"}],"action":"","property":"","from":"","to":"","reg":false,"x":590,"y":1080,"wires":[["c47ca5b2.5197c8"]]},{"id":"5b167e47.f3e8d","type":"ui_text_input","z":"1960dc1.e397c24","name":"","label":"End Time","tooltip":"","group":"1435e071.39b76","order":5,"width":6,"height":2,"passthru":true,"mode":"time","delay":"0","topic":"endTime","topicType":"msg","x":700,"y":840,"wires":[["53fd7840.d64688"]]},{"id":"c47ca5b2.5197c8","type":"function","z":"1960dc1.e397c24","name":"My SQL Database Request","func":"// only execute query if download button has been clicked\nif(msg.topic == \"download\" || msg.topic == \"query\"){\n    //var port= \"dewa_10\"; //replace with port no\n    /*var startTanggal = flow.get(\"startDate\");\n    var startWaktu = flow.get(\"startTime\");\n    \n    var endTanggal = flow.get(\"endDate\");\n    var endWaktu = flow.get(\"endTime\");\n    \n    var Start = startTanggal + \" \" + startWaktu;\n    var end = endTanggal + \" \" + endWaktu;*/\n    \n    var startDate = flow.get(\"startDate\");\n    var startTime = flow.get(\"startTime\");\n    var onDate = startDate + \" \" + startTime;\n    \n    var endDate = flow.get(\"endDate\");\n    var endTime = flow.get(\"endTime\");\n    var offDate = endDate + \" \" + endTime;\n\n    // check if date range is correct\n    if (new Date(endDate) >= new Date(startDate)) {\n        msg.topic = \"SELECT * FROM fuzzy WHERE datetime BETWEEN '\" + startDate + \"' AND '\" + endDate + \"' ORDER BY datetime limit 5000\";\n        return msg;\n    } else {\n        node.warn(\"end date is before start date.\");\n    }\n\n}\n\n","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":780,"y":980,"wires":[["3f503323.02092c"]]},{"id":"36f0730.0aaba8e","type":"inject","z":"1960dc1.e397c24","name":"query","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"query","payloadType":"date","x":610,"y":900,"wires":[["c47ca5b2.5197c8"]]},{"id":"f02683c1.d1bc1","type":"ui_button","z":"1960dc1.e397c24","d":true,"name":"","group":"1435e071.39b76","order":12,"width":0,"height":0,"passthru":false,"label":"button","tooltip":"","color":"","bgcolor":"","icon":"","payload":"","payloadType":"str","topic":"topic","topicType":"msg","x":980,"y":1140,"wires":[[]]},{"id":"1435e071.39b76","type":"ui_group","name":"Data Logger","tab":"48313a88.497314","order":3,"disp":true,"width":17,"collapse":false},{"id":"e882f2c3.e1f7","type":"MySQLdatabase","name":"Monitoring ","host":"127.0.0.1","port":"3306","db":"iot","tz":"","charset":"UTF8"},{"id":"48313a88.497314","type":"ui_tab","name":"monitoring","icon":"fa-desktop","order":1,"disabled":false,"hidden":false}]
5

You have not supplied all data requested

You also need to say what the issue is
here is the date and time selection as you save to context they do not need linking to the function. in the function i have added a check to make sure context is saved before continuing.

You will need to add the result of the sql query and state any other issues.

[{"id":"d22b232b.f0a248","type":"ui_date_picker","z":"76e18aba.066dc4","name":"","label":"Start Date","group":"1435e071.39b76","order":1,"width":6,"height":2,"passthru":true,"topic":"payload","topicType":"msg","x":270,"y":100,"wires":[["172b124d.d582ce"]]},{"id":"172b124d.d582ce","type":"change","z":"76e18aba.066dc4","name":"","rules":[{"t":"set","p":"startDate","pt":"flow","to":"$moment($$.payload).format(\"YYYY-MM-DD\")","tot":"jsonata"}],"action":"","property":"","from":"","to":"","reg":false,"x":520,"y":100,"wires":[[]]},{"id":"77c9ca5c.0b429c","type":"change","z":"76e18aba.066dc4","name":"","rules":[{"t":"set","p":"startTime","pt":"flow","to":"$moment($$.payload).format(\"HH:mm:ss\")","tot":"jsonata"}],"action":"","property":"","from":"","to":"","reg":false,"x":530,"y":140,"wires":[[]]},{"id":"cb3a635d.9c9518","type":"ui_text_input","z":"76e18aba.066dc4","name":"","label":"Start Time","tooltip":"","group":"1435e071.39b76","order":2,"width":6,"height":2,"passthru":true,"mode":"time","delay":"0","topic":"topic","topicType":"msg","x":280,"y":140,"wires":[["77c9ca5c.0b429c"]]},{"id":"f237b5e6.11e758","type":"ui_date_picker","z":"76e18aba.066dc4","name":"","label":"End Date","group":"1435e071.39b76","order":4,"width":6,"height":2,"passthru":true,"topic":"end_date","topicType":"msg","x":170,"y":200,"wires":[["7651853e.167cec"]]},{"id":"1fa442cb.68b57d","type":"ui_text_input","z":"76e18aba.066dc4","name":"","label":"End Time","tooltip":"","group":"1435e071.39b76","order":5,"width":6,"height":2,"passthru":true,"mode":"time","delay":"0","topic":"endTime","topicType":"msg","x":170,"y":240,"wires":[["780c222e.818ccc"]]},{"id":"7651853e.167cec","type":"change","z":"76e18aba.066dc4","name":"","rules":[{"t":"set","p":"endDate","pt":"flow","to":"$moment($$.payload).format(\"YYYY-MM-DD\")","tot":"jsonata"}],"action":"","property":"","from":"","to":"","reg":false,"x":380,"y":200,"wires":[[]]},{"id":"780c222e.818ccc","type":"change","z":"76e18aba.066dc4","name":"","rules":[{"t":"set","p":"endTime","pt":"flow","to":"$moment($$.payload).format(\"HH:mm:ss\")","tot":"jsonata"}],"action":"","property":"","from":"","to":"","reg":false,"x":380,"y":240,"wires":[[]]},{"id":"48d7ef00.476b18","type":"inject","z":"76e18aba.066dc4","name":"query","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"query","payloadType":"date","x":100,"y":320,"wires":[["4d73983a.5c8fd8"]]},{"id":"4d73983a.5c8fd8","type":"function","z":"76e18aba.066dc4","name":"My SQL Database Request","func":"// only execute query if download button has been clicked\nif(msg.topic == \"download\" || msg.topic == \"query\"){\n\n    var startDate = flow.get(\"startDate\") || false;\n    var startTime = flow.get(\"startTime\") || false;\n    \n    var endDate = flow.get(\"endDate\") || false;\n    var endTime = flow.get(\"endTime\") || false;\n\n    // check if date range is correct\n    if (startTime && startTime && endDate && endTime &&\n    new Date(endDate) >= new Date(startDate)) {\n        msg.topic = \"SELECT * FROM fuzzy WHERE datetime BETWEEN '\" + startDate + \" \" + startTime + \"' AND '\" + endDate + \" \" + endTime + \"' ORDER BY datetime limit 5000\";\n        return msg;\n    } else {\n        node.warn(\"end date is before start date. or context not set\");\n    }\n\n}\n\n","outputs":1,"noerr":0,"initialize":"","finalize":"","x":270,"y":400,"wires":[["f81d407d.98973"]]},{"id":"f81d407d.98973","type":"debug","z":"76e18aba.066dc4","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","statusVal":"","statusType":"auto","x":640,"y":340,"wires":[]},{"id":"1435e071.39b76","type":"ui_group","name":"Data Logger","tab":"48313a88.497314","order":3,"disp":true,"width":17,"collapse":false},{"id":"48313a88.497314","type":"ui_tab","name":"test","icon":"fa-desktop","order":1,"disabled":false,"hidden":false}]
6

thank u for function is work thanks u verry much , but i have question again

  1. how to edit/time picker dashboard with format hh:mm:ss , because currently the time picker format is hh:mm.
    this capture dashboard: image
7

This topic was automatically closed 60 days after the last reply. New replies are no longer allowed.