# How to combine date and time picker get data mysql

**URL:** <https://discourse.nodered.org/t/how-to-combine-date-and-time-picker-get-data-mysql/49524>\
**Category:** General\
**Created:** [9 August 2021 11:07 UTC](https://discourse.nodered.org/t/how-to-combine-date-and-time-picker-get-data-mysql/49524 "2021-08-09T11:07:28Z")\
**Posts on this page:** 6\
**Page:** 1

<div class="post-metadata">

**Author:** ![Apoki21](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/apoki21/32/44302_2.png) [@Apoki21](https://discourse.nodered.org/u/Apoki21)\
**Post date:** [9 August 2021 11:07 UTC](https://discourse.nodered.org/t/how-to-combine-date-and-time-picker-get-data-mysql/49524/1 "2021-08-09T11:07:28Z")

</div>

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

 ![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/6/f/6f6c44e4444d57ea951bcdf8e230a313d092b5d3.png)  
and this my function combine:  
 ![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/6/4/64a08f704f1c6905263cf47d823f13e95fc15628.png)  
This my dashboard grab:  
 ![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/9/6/9640834d9ea5754e8d3590b2f8cbc0798233a78c.png)  
This my format datetime on mysql  
 ![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/9/4/94144a82d380c8dda44b7ee527dd583bf7677076.png)

Thanks All

---

<div class="post-metadata">

**Author:** ![E1cid](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/e1cid/32/77971_2.png) [@E1cid](https://discourse.nodered.org/u/E1cid)\
**Post date:** [9 August 2021 11:55 UTC](https://discourse.nodered.org/t/how-to-combine-date-and-time-picker-get-data-mysql/49524/2 "2021-08-09T11:55:23Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![Apoki21](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/apoki21/32/44302_2.png) [@Apoki21](https://discourse.nodered.org/u/Apoki21)\
**Post date:** [9 August 2021 13:38 UTC](https://discourse.nodered.org/t/how-to-combine-date-and-time-picker-get-data-mysql/49524/4 "2021-08-09T13:38:09Z")

</div>

okay thanks u for respon, this my flow node

```auto
[{"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}]

```

---

<div class="post-metadata">

**Author:** ![E1cid](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/e1cid/32/77971_2.png) [@E1cid](https://discourse.nodered.org/u/E1cid)\
**Post date:** [9 August 2021 14:22 UTC](https://discourse.nodered.org/t/how-to-combine-date-and-time-picker-get-data-mysql/49524/5 "2021-08-09T14:22:44Z")

</div>

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.

```auto
[{"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}]

```

---

<div class="post-metadata">

**Author:** ![Apoki21](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/apoki21/32/44302_2.png) [@Apoki21](https://discourse.nodered.org/u/Apoki21)\
**Post date:** [9 August 2021 17:27 UTC](https://discourse.nodered.org/t/how-to-combine-date-and-time-picker-get-data-mysql/49524/6 "2021-08-09T17:27:53Z")

</div>

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](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/6/8/689987fd996277b5a2b02d44cb486ea1c1a10256.png)

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/1X/d073cd938eafa2e558d7c2cd59003b3ef4963033.png) [@system](https://discourse.nodered.org/u/system)\
**Post date:** [8 October 2021 17:28 UTC](https://discourse.nodered.org/t/how-to-combine-date-and-time-picker-get-data-mysql/49524/7 "2021-10-08T17:28:23Z")

</div>

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