Node-red and mySQL show different output

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?

SELECT DATE(tstamp) FROM Temp WHERE data = ( SELECT MIN(data) FROM Temp ) AND topic = 'TT_Temp' AND tstamp > NOW() - INTERVAL 7 DAY

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.

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?

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.

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

_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"

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?

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 :thinking:

AND what I forgot to mention: I can extract the time from the DateTime format but not the Date? :thinking:
So the mysql code for the time works well in mysql and with node-red!
It is not logical to me.

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


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 :wink: