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.
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.
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
topic: "SELECT MIN(data), DATE(tstamp) FROM Temp WHERE topic = 'WC_Temp' AND tstamp > NOW() - INTERVAL 7 DAY "
let day = msg.payload["DATE(tstamp)"].getDay()
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.
It is down to whoever decided that the return value from sql that asks for
Thanks for your help. I installed the moment node and it works fine.