Node-red and mySQL show different output

General
1

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

2

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.

3

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?

4

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.

5

Hey Colin,

This is the output of the debug node:

debug

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"

6

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?

7

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.

8

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

9

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