How to get date time to mysql

[TrySorn]

hello everyond, my name is Sorn and i need your help. i have problem about save datetime (Dec 10 2020 17:26:34 GMT+0700 (Indochina Time)) from node-red to mysql and i get some error that is "Error: ER_PARSE_ERROR: You have an error in your SQL syntax; ".i dont understand about datetime data type in mysql. i choose vary type such as varchar,date,datetime but not accomplish. II. second problem is how to send only YYYY-MM-DD time (2020-12-10 21.11.50) to mysql.Colud someone help me please? thank you
regard.

var date=new Date().toString();
 msg.topic = "INSERT INTO `test-sequence` VALUES ("+new Date().toString()+","+msg.payload["Vrms"]+","+msg.payload["Irms"]+","+msg.payload["Power"]+","+ msg.payload["Energy"] +","+ msg.payload["PF"] +","+ msg.payload["Frequency"] +")";
 return msg;

[Steve-Mcl]

You need quotes around the datetime string. (read the topic you send carefully)

Also you should probably use toISOString

Also, I believe you can simply send the epoch (e.g. Date.now()) (without quotes as epoch is a number)

Lastly, to simplify string concatenation, I would recommend using string template literals e.g...

 msg.topic = `INSERT INTO \`test-sequence\` VALUES ( '${(new Date).toISOString()}' , ${msg.payload["Vrms"]} , ${msg.payload["Irms"]} , ${msg.payload["Power"]} , ${msg.payload["Energy"]} , ${msg.payload["PF"]} , ${msg.payload["Frequency"]} )`;
 return msg;

(note as a template literal uses backticks we have to escape the backticks around the table name)

[Colin]

Even better, if you always want to save the current time then make sure the column is type TIMESTAMP and set it DEFAULT CURRENT_TIMESTAMP. Then you don't need to provide a timestamp at all.

[TrySorn]

@Steve-Mcl

msg.topic = `INSERT INTO \`test-sequence\` VALUES ( '${(new Date).toISOString()}' , ${msg.payload["Vrms"]} , ${msg.payload["Irms"]} , ${msg.payload["Power"]} , ${msg.payload["Energy"]} , ${msg.payload["PF"]} , ${msg.payload["Frequency"]} )`;
 return msg;

thank you verymuch for answer, when i copy your code i had error like picture below.

i change something it show like this

var date = new Date().toISOString();
 msg.topic = "INSERT INTO `test-sequence` VALUES ("+date+","+msg.payload["Vrms"]+","+msg.payload["Irms"]+","+msg.payload["Power"]+","+ msg.payload["Energy"] +","+ msg.payload["PF"] +","+ msg.payload["Frequency"] +")";
 return msg;

finally i had smae error to mysql

[Steve-Mcl]

@TrySorn

thank you verymuch for answer, when i copy your code i had error like picture below.

That is not an error. its a warning as your (old node-red) editor version doesnt understand the syntax.

IT DOES WORK

Just try what a wrote.

[TrySorn]

Thank you verymuch. before i do that my flow is work. it can send data to mysql accomplish but when add column in mysql like you tell i had same error

this is my function node code

msg.topic = "INSERT INTO `test-sequence` VALUES ("+msg.payload["Vrms"]+","+msg.payload["Irms"]+","+msg.payload["Power"]+","+ msg.payload["Energy"] +","+ msg.payload["PF"] +","+ msg.payload["Frequency"] +")";
 return msg;

and this is my column

[Colin]

@TrySorn

you tell i had same error

Feed the function node output into a debug node set to show Complete Message and see what is in msg.topic.

[Edit] You would be much better to use the backtick syntax suggested earlier.,,It is much easier to get right. Do not bother feeding the message into the sql node until the topic coming from the function node is what you expect.

[TrySorn]

oh is done now thank you very much Mr.Steve-Mcl. but i dont understand stucture of this time because time isnt correct.

Now my time must show like this 2020-12-10 22.14.30 what mean of T and Z

[Steve-Mcl]

@TrySorn

what mean of T and Z

Z = ZULU (info)

That is an international standard time (ISO format) - it represents UTC and this is how you REALLY should store time in a database. Only when you present it to a user do you convert to local time.

Read up on "why you should store utc in database"

If you want to store local time I would strongly recommend you at least also store UTC time alongside it (you will thank me when timezone changes or daylight savings changes or an international user looks at your data)

[TrySorn]

OK, i get it. i will learn more for about how to write correct structure. thank you verymuch Mr.Colin.

[TrySorn]

i get it now. your word very real because ISO time can convert any timezone and very correct time. I very appreciate you.Thankyou
i will store two time that is ISO time , GMT+7 time. i think the code will be like this

  msg.topic = `INSERT INTO \`test-sequence\` VALUES ( '${(new Date).toISOString()}', '${(new Date).toTimeString()}' , ${msg.payload["Vrms"]} , ${msg.payload["Irms"]} , ${msg.payload["Power"]} , ${msg.payload["Energy"]} , ${msg.payload["PF"]} , ${msg.payload["Frequency"]} )`;
 return msg;

it show this.

can i show only 2020-12-10 22:58:22