# Node-red and mySQL show different output
**URL:** https://discourse.nodered.org/t/node-red-and-mysql-show-different-output/19502
**Category:** General
**Created:** [23 December 2019 13:35 UTC](https://discourse.nodered.org/t/node-red-and-mysql-show-different-output/19502 "2019-12-23T13:35:30Z")
**Posts on this page:** 9
**Page:** 1
### Author:  [@stefanp](https://discourse.nodered.org/u/stefanp)
#### Post date: [23 December 2019 13:35 UTC](https://discourse.nodered.org/t/node-red-and-mysql-show-different-output/19502/1 "2019-12-23T13:35:30Z")
I have an issue with node-red-mysql and the actual mysql code.
I use phpMyAdmin to test mysql codes and I get different results!
I want to select the date in tstamp (my timestamp in DATETIME format) where the min temperature occured in the last 7 days.
The format in phpMyAdmin is correct and shows only the date (2019-12-20)
Then I put in the code in Node-red (function node) the output is different it shows (2019-12-20T00:00:00.000Z)
So has anyone a clue what I am missing?
CODE:
SELECT DATE(tstamp) FROM Temp WHERE data = ( SELECT MIN(data) FROM Temp ) AND topic = 'TT\_Temp' AND tstamp \> NOW() - INTERVAL 7 DAY
---
### Author:  [@afelix](https://discourse.nodered.org/u/afelix)
#### Post date: [23 December 2019 14:05 UTC](https://discourse.nodered.org/t/node-red-and-mysql-show-different-output/19502/2 "2019-12-23T14:05:27Z")
The response is actually the same, but formatted differently. 2019-12-20 is just the date, and `2019-12-20T00:00:00.000Z` is the exact same date, but at exactly midnight in the time zone UTC (Zulu time hence the Z at the end). Depending on the format in which node-red returns it, you can format it to the format you're looking for, but they represent the exact same value.
---
### Author:  [@stefanp](https://discourse.nodered.org/u/stefanp)
#### Post date: [23 December 2019 14:26 UTC](https://discourse.nodered.org/t/node-red-and-mysql-show-different-output/19502/3 "2019-12-23T14:26:53Z")
Hey afelix,
thanks for your quick reply. The "T00:00:00.000Z" is after I have the SELECTED DATE command inserted , otherwise it will be the correct time and not the zeros.
Exact TIMEDATE = 2019-12-20T10:55:00.000Z
So these zeros come from my DATE selection command which is retured form the mysql-node.
Is it possible that mysql-node messes up my date selection and turns it in a Date and time?
How can I easily fix that?
---
### Author:  [@Colin](https://discourse.nodered.org/u/Colin)
#### Post date: [23 December 2019 14:45 UTC](https://discourse.nodered.org/t/node-red-and-mysql-show-different-output/19502/4 "2019-12-23T14:45:14Z")
Feed the output from the sql node into a debug node and show us what it says including the header in the debug output. Then we can see what type the value is.
---
### Author:  [@stefanp](https://discourse.nodered.org/u/stefanp)
#### Post date: [23 December 2019 14:54 UTC](https://discourse.nodered.org/t/node-red-and-mysql-show-different-output/19502/5 "2019-12-23T14:54:42Z")
Hey Colin,
This is the output of the debug node:
SELECT MIN(data), DATE(tstamp) FROM Temp WHERE topic = 'WC\_Temp' AND tstamp \> NOW() - INTERVAL 7 DAY : msg : Object
object
\_msgid: "2149b152.7f81fe"
topic: "SELECT MIN(data), DATE(tstamp) FROM Temp WHERE topic = 'WC\_Temp' AND tstamp \> NOW() - INTERVAL 7 DAY "
payload: array[1]
0: object
MIN(data): 20.05
DATE(tstamp): "2019-12-21T00:00:00.000Z"
---
### Author:  [@Colin](https://discourse.nodered.org/u/Colin)
#### Post date: [23 December 2019 15:22 UTC](https://discourse.nodered.org/t/node-red-and-mysql-show-different-output/19502/6 "2019-12-23T15:22:11Z")
I am pretty sure (though it is not easy to tell from the debug) that the date/time value is actually a javascript Date object. Which would be entirely logical. If so then you should, in a function node, be able to do, for example
`let day = msg.payload[0]["DATE(tstamp)"].getDay()`
which will return the day of the month. See the docs for javascript Date to see the other access methods. Alternatively you could feed that into a node-red-contrib-moment node to format it however you want. What exactly do you want to do with it?
---
### Author:  [@stefanp](https://discourse.nodered.org/u/stefanp)
#### Post date: [23 December 2019 16:36 UTC](https://discourse.nodered.org/t/node-red-and-mysql-show-different-output/19502/7 "2019-12-23T16:36:59Z")
Hey Colin,
thanks for your suggestion I will give it a try!
It is very simple, I just want to display the day and month without the time because it is not that important to me.
What I don't understand is that the code in mysql (phpMyAdmin) shows the right format but with node-red the result is different 🤔
AND what I forgot to mention: I can extract the time from the DateTime format but not the Date? 🤔
So the mysql code for the time works well in mysql and with node-red!
It is not logical to me.
---
### Author:  [@Colin](https://discourse.nodered.org/u/Colin)
#### Post date: [23 December 2019 17:23 UTC](https://discourse.nodered.org/t/node-red-and-mysql-show-different-output/19502/8 "2019-12-23T17:23:54Z")
It is down to whoever decided that the return value from sql that asks for `GET DATE()` should be a javascript date/time object. Javascript does not have a type specific to holding just a date without a time so I think using a javascript date/time object was probably the optimum solution. Perhaps php has a type specific to just a date, I don't know. The advantage is that now you can format the date exactly as you want it mm/dd/yy or dd/mm/yy or dd-mm-yyyy or whatever using the javascript methods or the moment node I mentioned earlier. If the node had returned a string then it would have been much more difficult to convert to your required format
---
### Author:  [@stefanp](https://discourse.nodered.org/u/stefanp)
#### Post date: [23 December 2019 21:00 UTC](https://discourse.nodered.org/t/node-red-and-mysql-show-different-output/19502/9 "2019-12-23T21:00:25Z")
@Colin
Thanks for your help. I installed the moment node and it works fine.
I will look at the javascript so I have an idea how that works too 😉