# Node date picker y mysql

**URL:** <https://discourse.nodered.org/t/node-date-picker-y-mysql/64372>\
**Category:** General\
**Created:** [25 June 2022 21:49 UTC](https://discourse.nodered.org/t/node-date-picker-y-mysql/64372 "2022-06-25T21:49:04Z")\
**Posts on this page:** 7\
**Page:** 1

<div class="post-metadata">

**Author:** ![JuanVA](https://avatars.discourse-cdn.com/v4/letter/j/e99b99/32.png) [@JuanVA](https://discourse.nodered.org/u/JuanVA)\
**Post date:** [25 June 2022 21:49 UTC](https://discourse.nodered.org/t/node-date-picker-y-mysql/64372/1 "2022-06-25T21:49:04Z")

</div>

Good everyone, Someone for the doubts can guide me on how to use the node "date picker" and mysql or if you have any example already made to guide me from that, is that I need to do a data filtering from a date "x" to a date "y". Thank you very much.

---

<div class="post-metadata">

**Author:** ![jbudd](https://avatars.discourse-cdn.com/v4/letter/j/5f8ce5/32.png) [@jbudd](https://discourse.nodered.org/u/jbudd)\
**Post date:** [26 June 2022 00:55 UTC](https://discourse.nodered.org/t/node-date-picker-y-mysql/64372/2 "2022-06-26T00:55:31Z")

</div>

[This thread](https://discourse.nodered.org/t/converting-datetime-to-dd-mm-yyyy-hh-mm/36012) describes converting the date picker output format.

I adapted it to convert the date to YYYY-MM-HH and constructed this select statement which works for me:

```auto
select * from socketevent where ontime between "2022-06-02" and "22-06-05";
+------+---------------------+--------+---------------------+--------+------+
| id | ontime | onkwh | offtime | offkwh | temp |
+------+---------------------+--------+---------------------+--------+------+
| 5445 | 2022-06-02 06:56:52 | 31.12 | 2022-06-02 07:26:52 | 31.302 | NULL |
| 5446 | 2022-06-02 08:36:55 | 31.302 | 2022-06-02 08:43:08 | 31.396 | NULL |
| 5447 | 2022-06-02 13:55:50 | 31.397 | 2022-06-02 14:08:09 | 31.528 | NULL |
| 5448 | 2022-06-03 07:38:25 | 31.528 | 2022-06-03 08:08:26 | 31.688 | NULL |
| 5449 | 2022-06-03 08:08:34 | 31.69 | 2022-06-03 08:20:18 | 31.737 | NULL |
| 5450 | 2022-06-04 07:48:50 | 31.737 | 2022-06-04 08:18:50 | 31.934 | NULL |
| 5451 | 2022-06-04 08:35:52 | 31.935 | 2022-06-04 09:05:52 | 32.048 | NULL |

```

Note that "2022-06-02" is interpreted by the database as "2022-06-02 00:00" so records from 2nd June are retrieved, but no records from 5th June are.

---

<div class="post-metadata">

**Author:** ![rakgupta](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/rakgupta/32/42535_2.png) [@rakgupta](https://discourse.nodered.org/u/rakgupta)\
**Post date:** [27 June 2022 12:08 UTC](https://discourse.nodered.org/t/node-date-picker-y-mysql/64372/3 "2022-06-27T12:08:32Z")

</div>

Is your 2nd date (date to) formatted correctly? ("22-06-05" vs "2022-06-05")

---

<div class="post-metadata">

**Author:** ![rakgupta](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/rakgupta/32/42535_2.png) [@rakgupta](https://discourse.nodered.org/u/rakgupta)\
**Post date:** [27 June 2022 12:39 UTC](https://discourse.nodered.org/t/node-date-picker-y-mysql/64372/4 "2022-06-27T12:39:31Z")

</div>

> [@jbudd](#):
>
> Note that "2022-06-02" is interpreted by the database as "2022-06-02 00:00" so records from 2nd June are retrieved, but no records from 5th June are.

Also, if your date format is correct, wouldn't "22-06-05" be interpreted as 2022-06-05 00:00:00? So nothing \> that date/time would be selected (effectively leaving out all data for 2022-06-05)

---

<div class="post-metadata">

**Author:** ![jbudd](https://avatars.discourse-cdn.com/v4/letter/j/5f8ce5/32.png) [@jbudd](https://discourse.nodered.org/u/jbudd)\
**Post date:** [27 June 2022 13:03 UTC](https://discourse.nodered.org/t/node-date-picker-y-mysql/64372/5 "2022-06-27T13:03:51Z")

</div>

Exactly so.

And I made a mistake with my second date, should indeed have used 4 digits for the year. Thanks to everyone who pointed this out!

---

<div class="post-metadata">

**Author:** ![rakgupta](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/rakgupta/32/42535_2.png) [@rakgupta](https://discourse.nodered.org/u/rakgupta)\
**Post date:** [27 June 2022 21:19 UTC](https://discourse.nodered.org/t/node-date-picker-y-mysql/64372/6 "2022-06-27T21:19:30Z")

</div>

> [@JuanVA](#):
>
> how to use the node "date picker" and mysql

Below are two pieces of code that use the date picker and create a mySQL query to select data within a date range:

1. Setting the date range to a flow variable (you don't need to do this but since I use the date for different flows, I copy them to a flow variable)

```auto
[{"id":"8c60682c.a7a6d","type":"function","z":"3fad0f28.757898","g":"42813315.c49ccc","name":"Preset date range","func":"//Fix number of miliseconds in a day\nvar millisInDays = 86400000;\n\n//Create 2nd output message\nmsg1={}\nmsg1.payload = []\n\nmsg2={}\nmsg2.payload = []\n\n//Calculate from and to dates\n\nvar toDate = new Date()\nvar curDate = toDate.getDate()\nvar fromDate = new Date()\n\n//Set from date as 1st of the current month\nfromDate = fromDate - ((curDate-1)* millisInDays)\n\nmsg1.payload = fromDate;\nmsg2.payload = toDate;\n\nreturn [msg1,msg2];","outputs":2,"noerr":0,"initialize":"","finalize":"","libs":[],"x":892.6666564941406,"y":2704,"wires":[["a294dc9b.224eb8"],["633f373e.17de7"]]},{"id":"a294dc9b.224eb8","type":"ui_date_picker","z":"3fad0f28.757898","g":"42813315.c49ccc","name":"Date From","label":"Date From:","group":"67a39dee.c646e4","order":1,"width":"5","height":1,"passthru":true,"topic":"","topicType":"str","className":"","x":1092.6667175292969,"y":2655,"wires":[["701acdd3.436f34"]],"inputLabels":["Today"]},{"id":"633f373e.17de7","type":"ui_date_picker","z":"3fad0f28.757898","g":"42813315.c49ccc","name":"Date To","label":"Date To:","group":"67a39dee.c646e4","order":2,"width":"5","height":1,"passthru":true,"topic":"","topicType":"str","className":"","x":1092.3334045410156,"y":2751.3333740234375,"wires":[["181040cb.4583df"]],"inputLabels":["Today"]},{"id":"701acdd3.436f34","type":"moment","z":"3fad0f28.757898","g":"42813315.c49ccc","name":"Format from date","topic":"","input":"","inputType":"msg","inTz":"ETC/GMT","adjAmount":0,"adjType":"days","adjDir":"add","format":"YYYY-MM-DD","locale":"en_US","output":"fromDate","outputType":"flow","outTz":"America/Chicago","x":1299.3336486816406,"y":2654.3333740234375,"wires":[[]]},{"id":"181040cb.4583df","type":"moment","z":"3fad0f28.757898","g":"42813315.c49ccc","name":"Format to date","topic":"","input":"","inputType":"msg","inTz":"ETC/GMT","adjAmount":0,"adjType":"days","adjDir":"add","format":"YYYY-MM-DD","locale":"en_US","output":"toDate","outputType":"flow","outTz":"America/Chicago","x":1290.3335266113281,"y":2750.3333740234375,"wires":[[]]},{"id":"67a39dee.c646e4","type":"ui_group","name":"Date Range","tab":"660a703d.ea902","order":2,"disp":true,"width":"26","collapse":false},{"id":"660a703d.ea902","type":"ui_tab","name":"Tesla Solar Summary","icon":"fa-bar-chart","order":4,"disabled":false,"hidden":false}]

```

 ![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/a/9/a9e1c410c4840bfbf1a89c68a806de5568e88913.jpeg)

The flow uses the "node-red-contrib-moment" node to format the dates.

1. Build the SQL query using the dates. I am including the whole SQL query, but the relevant section is in the last 2 lines in the function node that build the query:

sqlString += "date (created) \>= '"+flow.get("fromDate")+"' "  
sqlString += "and date (created) \<= '"+flow.get("toDate")+"' "

the mySQL DATE() function returns the date in YYYY-MM-DD format, so the time is ignored and only the date records are matched.

```auto
//Build SQL query

var sqlString = ""

sqlString += "select round((load_home_imp_per - load_home_exp_per)/1000,2) as 'totalHome', " 
sqlString += "round((site_grid_imp_per)/1000,2) as 'fromGrid', " 
sqlString += "(round((solar_exp_per - solar_imp_per)/1000,2)-round((site_grid_exp_per)/1000,2)-round(battery_pwl_imp_per/1000,2)) as 'fromSolar', " 
sqlString += "round(battery_pwl_exp_per/1000,2) as 'fromBattery', " 
sqlString += "round((solar_exp_per - solar_imp_per)/1000,2) as 'totalSolar', " 
sqlString += "(select fromSolar) as 'toHome', "
sqlString += "round((site_grid_exp_per)/1000,2) as 'toGrid', " 
sqlString += "round(battery_pwl_imp_per/1000,2) as 'toBattery', " 
sqlString += "round(((select fromGrid)/(select totalHome))*100,2) as 'fromGridPct', "
sqlString += "round(((select fromSolar)/(select totalHome))*100,2) as 'fromSolarPct', "
sqlString += "round(((select fromBattery)/(select totalHome))*100,2) as 'fromBatteryPct', "
sqlString += "round(((select toHome)/(select totalSolar))*100,2) as 'toHomePct', "
sqlString += "round(((select toBattery)/(select totalSolar))*100,2) as 'toBatteryPct', "
sqlString += "round(((select toGrid)/(select totalSolar))*100,2) as 'toGridPct', "
sqlString += "100 as 'totalHomePct', "
sqlString += "100 as 'totalSolarPct', "
sqlString += "date_format(created,'%b-%e') as 'labels', "
sqlString += "date(created)as 'created' "
sqlString += "from powerwall_cumm where "
sqlString += "date (created) >= '"+flow.get("fromDate")+"' "
sqlString += "and date (created) <= '"+flow.get("toDate")+"' "

msg.topic = sqlString;
return msg;

```

The output of the function node (the query MUST be in msg.topic) is sent to the mySQL node that executes the query and returns the results.

Hope this helps.

---

<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:** [26 August 2022 21:19 UTC](https://discourse.nodered.org/t/node-date-picker-y-mysql/64372/7 "2022-08-26T21:19:52Z")

</div>

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