# MySQL query for a range of dates

**URL:** <https://discourse.nodered.org/t/mysql-query-for-a-range-of-dates/31269>\
**Category:** General\
**Created:** [7 August 2020 13:59 UTC](https://discourse.nodered.org/t/mysql-query-for-a-range-of-dates/31269 "2020-08-07T13:59:03Z")\
**Posts on this page:** 9\
**Page:** 1

<div class="post-metadata">

**Author:** ![kumarsemal](https://avatars.discourse-cdn.com/v4/letter/k/7feea3/32.png) [@kumarsemal](https://discourse.nodered.org/u/kumarsemal)\
**Post date:** [7 August 2020 13:59 UTC](https://discourse.nodered.org/t/mysql-query-for-a-range-of-dates/31269/1 "2020-08-07T13:59:03Z")

</div>

Hello Sir,  
I stuck in a mysql query. I want to pick two dates and want to display the data in tabular form on dashboard after fetching data from database. I ran the query in mysql workbench and it was successfull. But, while running the same query in msg.topic in function node the result shows msg.payload[0]. The flows is exported here as:  
`[{"id":"ea171992.c2c538","type":"function","z":"99e51cc7.c42cb","name":"","func":"var start = msg.payload.from;\nvar end = msg.payload.to;\nmsg.topic =\"SELECT * FROM random where data_date between \"+start+\" and \"+end+\";\";\nreturn msg;\n","outputs":1,"noerr":0,"initialize":"","finalize":"","x":330,"y":80,"wires":[["4a583b85.12a814"]]},{"id":"4a583b85.12a814","type":"mysql","z":"99e51cc7.c42cb","mydb":"4408c380.b7cffc","name":"","x":500,"y":80,"wires":[["cae47677.83b378","4e657f46.64fb7"]]},{"id":"cae47677.83b378","type":"debug","z":"99e51cc7.c42cb","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","statusVal":"","statusType":"auto","x":670,"y":80,"wires":[]},{"id":"4e657f46.64fb7","type":"ui_table","z":"99e51cc7.c42cb","group":"4d5e7557.929f7c","name":"","order":3,"width":0,"height":0,"columns":[],"outputs":0,"cts":false,"x":640,"y":160,"wires":[]},{"id":"a8ca02ab.c91c6","type":"ui_template","z":"99e51cc7.c42cb","group":"4d5e7557.929f7c","name":"form_date","order":4,"width":0,"height":0,"format":"<p>From: <input type=\"text\" name=\"from\" value=\"from\" ng-model=\"from\" placeholder=\"YYYY-MM-DD\" /></p>\n<p>To: <input type=\"text\" name=\"to\" value=\"to\" ng-model=\"to\" placeholder=\"YYYY-MM-DD\"/></p>\n<md-button ng-click=\"send({payload:{from:from, to:to}})\">Submit</md-button>\n","storeOutMessages":true,"fwdInMessages":true,"resendOnRefresh":true,"templateScope":"local","x":120,"y":80,"wires":[["ea171992.c2c538"]]},{"id":"4408c380.b7cffc","type":"MySQLdatabase","z":"","name":"dischargeDB","host":"localhost","port":"3306","db":"nathpa","tz":"GMT + 5:30"},{"id":"4d5e7557.929f7c","type":"ui_group","z":"","name":"Sem","tab":"3871e724.85cf98","order":1,"disp":true,"width":"4","collapse":false},{"id":"3871e724.85cf98","type":"ui_tab","z":"","name":"Home","icon":"dashboard","disabled":false,"hidden":false}]`  
Please suggest me where i am wrong?  
Thanks in advance.

---

<div class="post-metadata">

**Author:** ![zenofmud](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/zenofmud/32/316_2.png) [@zenofmud](https://discourse.nodered.org/u/zenofmud)\
**Post date:** [7 August 2020 14:11 UTC](https://discourse.nodered.org/t/mysql-query-for-a-range-of-dates/31269/2 "2020-08-07T14:11:36Z")

</div>

If you put a `debug` node on the output of the `function` node - set to display the Complete msg object - what does it show?

Look at the msg.topic and how does it compare with the query that works in mysql workbench?

---

<div class="post-metadata">

**Author:** ![kumarsemal](https://avatars.discourse-cdn.com/v4/letter/k/7feea3/32.png) [@kumarsemal](https://discourse.nodered.org/u/kumarsemal)\
**Post date:** [7 August 2020 14:43 UTC](https://discourse.nodered.org/t/mysql-query-for-a-range-of-dates/31269/3 "2020-08-07T14:43:07Z")

</div>

Thanks for prompt reply sir.  
I put the debug node and the result is " 8/7/2020, 8:10:40 PM[node: b8d50e4d.1403f](http://localhost:1880/#)SELECT \* FROM random where data\_date between 2020-07-01 and 2020-07-29; : msg : Object

object

payload: object

socketid: "bWaTyjxjRMIMTg7wAAAC"

\_msgid: "62e54e5d.4da2e"

topic: "SELECT \* FROM random where data\_date between 2020-07-01 and 2020-07-29;"". The flow is as:

```auto
[{"id":"ea171992.c2c538","type":"function","z":"99e51cc7.c42cb","name":"","func":"var start = msg.payload.from;\nvar end = msg.payload.to;\nmsg.topic =\"SELECT * FROM random where data_date between \"+start+\" and \"+end+\";\";\nreturn msg;\n","outputs":1,"noerr":0,"initialize":"","finalize":"","x":330,"y":80,"wires":[["4a583b85.12a814","b8d50e4d.1403f"]]},{"id":"4a583b85.12a814","type":"mysql","z":"99e51cc7.c42cb","mydb":"4408c380.b7cffc","name":"","x":500,"y":80,"wires":[["cae47677.83b378","4e657f46.64fb7"]]},{"id":"cae47677.83b378","type":"debug","z":"99e51cc7.c42cb","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","statusVal":"","statusType":"auto","x":670,"y":80,"wires":[]},{"id":"4e657f46.64fb7","type":"ui_table","z":"99e51cc7.c42cb","group":"4d5e7557.929f7c","name":"","order":3,"width":0,"height":0,"columns":[],"outputs":0,"cts":false,"x":640,"y":160,"wires":[]},{"id":"a8ca02ab.c91c6","type":"ui_template","z":"99e51cc7.c42cb","group":"4d5e7557.929f7c","name":"form_date","order":4,"width":0,"height":0,"format":"<p>From: <input type=\"text\" name=\"from\" value=\"from\" ng-model=\"from\" placeholder=\"YYYY-MM-DD\" /></p>\n<p>To: <input type=\"text\" name=\"to\" value=\"to\" ng-model=\"to\" placeholder=\"YYYY-MM-DD\"/></p>\n<md-button ng-click=\"send({payload:{from:from, to:to}})\">Submit</md-button>\n","storeOutMessages":true,"fwdInMessages":true,"resendOnRefresh":true,"templateScope":"local","x":120,"y":80,"wires":[["ea171992.c2c538"]]},{"id":"b8d50e4d.1403f","type":"debug","z":"99e51cc7.c42cb","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","statusVal":"","statusType":"auto","x":460,"y":160,"wires":[]},{"id":"4408c380.b7cffc","type":"MySQLdatabase","z":"","name":"dischargeDB","host":"localhost","port":"3306","db":"nathpa","tz":"GMT + 5:30"},{"id":"4d5e7557.929f7c","type":"ui_group","z":"","name":"Sem","tab":"3871e724.85cf98","order":1,"disp":true,"width":"4","collapse":false},{"id":"3871e724.85cf98","type":"ui_tab","z":"","name":"Home","icon":"dashboard","disabled":false,"hidden":false}]

```

---

<div class="post-metadata">

**Author:** ![zenofmud](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/zenofmud/32/316_2.png) [@zenofmud](https://discourse.nodered.org/u/zenofmud)\
**Post date:** [7 August 2020 15:39 UTC](https://discourse.nodered.org/t/mysql-query-for-a-range-of-dates/31269/4 "2020-08-07T15:39:21Z")

</div>

You don't need to provide the flow a second time. So the debug shows the query is:  
`SELECT * FROM random where data_date between 2020-07-01 and 2020-07-29;`  
now what does the query you used in mysql workbench look like?

Compare the two...are there any differences?

---

<div class="post-metadata">

**Author:** ![kumarsemal](https://avatars.discourse-cdn.com/v4/letter/k/7feea3/32.png) [@kumarsemal](https://discourse.nodered.org/u/kumarsemal)\
**Post date:** [8 August 2020 04:23 UTC](https://discourse.nodered.org/t/mysql-query-for-a-range-of-dates/31269/5 "2020-08-08T04:23:26Z")

</div>

Yes Sir,  
There is difference in date value. In mysql workbench  
SELECT \* FROM random where data\_date between '2020-07-01' and '2020-07-29';  
date values are withing quotes while node red function node these are without quotes. I think this might be reason. So, please suggest me how to include quotes as these are values received from user interface node.

---

<div class="post-metadata">

**Author:** ![bakman2](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/bakman2/32/6207_2.png) [@bakman2](https://discourse.nodered.org/u/bakman2)\
**Post date:** [8 August 2020 04:47 UTC](https://discourse.nodered.org/t/mysql-query-for-a-range-of-dates/31269/6 "2020-08-08T04:47:56Z")

</div>

Try changing the statement:

```auto
msg.topic ="SELECT * FROM random where data_date between '"+start+"' and '"+end+"';";

```

---

<div class="post-metadata">

**Author:** ![kumarsemal](https://avatars.discourse-cdn.com/v4/letter/k/7feea3/32.png) [@kumarsemal](https://discourse.nodered.org/u/kumarsemal)\
**Post date:** [8 August 2020 05:43 UTC](https://discourse.nodered.org/t/mysql-query-for-a-range-of-dates/31269/7 "2020-08-08T05:43:48Z")

</div>

Thank you so much sir.  
It worked.  
Thank you once again.

---

<div class="post-metadata">

**Author:** ![zenofmud](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/zenofmud/32/316_2.png) [@zenofmud](https://discourse.nodered.org/u/zenofmud)\
**Post date:** [8 August 2020 09:51 UTC](https://discourse.nodered.org/t/mysql-query-for-a-range-of-dates/31269/8 "2020-08-08T09:51:51Z")

</div>

> [@kumarsemal](#):
>
> There is difference in date value. In mysql workbench  
> SELECT \* FROM random where data\_date between '2020-07-01' and '2020-07-29';  
> date values are withing quotes while node red function node these are without quotes. I think this might be reason.

This is why I had you add the `debug` node - so you could see the difference and then realize that you were not running the same query. Using the `debug` node is a great way to see what you are `actually` doing vrs what you `think` you are doing 😄

---

<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:** [7 October 2020 09:51 UTC](https://discourse.nodered.org/t/mysql-query-for-a-range-of-dates/31269/9 "2020-10-07T09:51:56Z")

</div>

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