# How to get date time to mysql

**URL:** <https://discourse.nodered.org/t/how-to-get-date-time-to-mysql/37299>\
**Category:** General\
**Created:** [10 December 2020 14:20 UTC](https://discourse.nodered.org/t/how-to-get-date-time-to-mysql/37299 "2020-12-10T14:20:40Z")\
**Posts on this page:** 12\
**Page:** 1

<div class="post-metadata">

**Author:** ![TrySorn](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/trysorn/32/33389_2.png) [@TrySorn](https://discourse.nodered.org/u/TrySorn)\
**Post date:** [10 December 2020 14:20 UTC](https://discourse.nodered.org/t/how-to-get-date-time-to-mysql/37299/1 "2020-12-10T14:20:40Z")

</div>

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.

```auto
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;

```

![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/3/1/31599abd00013302579ba8953f29aefae112d1b7.png)  
 ![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/3/e/3e307dd6e44b0617a53aad9377e77c6491253614.png)

 ![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/d/3/d34a0c2e3d99da0ba2d6f9341b4b151a5a869483.png)

---

<div class="post-metadata">

**Author:** ![Steve-Mcl](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/steve-mcl/32/4826_2.png) [@Steve-Mcl](https://discourse.nodered.org/u/Steve-Mcl)\
**Post date:** [10 December 2020 14:36 UTC](https://discourse.nodered.org/t/how-to-get-date-time-to-mysql/37299/2 "2020-12-10T14:36:04Z")

</div>

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

Also you should probably use [toISOString](https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Global_Objects/Date/toISOString)

Also, I believe you can simply send the epoch (e.g. [`Date.now()`](https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Global_Objects/Date/now)) (without quotes as epoch is a number)

Lastly, to simplify string concatenation, I would recommend using [string template literals](https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Template_literals) e.g...

```auto
 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)_

---

<div class="post-metadata">

**Author:** ![Colin](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/colin/32/17040_2.png) [@Colin](https://discourse.nodered.org/u/Colin)\
**Post date:** [10 December 2020 14:42 UTC](https://discourse.nodered.org/t/how-to-get-date-time-to-mysql/37299/3 "2020-12-10T14:42:16Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![TrySorn](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/trysorn/32/33389_2.png) [@TrySorn](https://discourse.nodered.org/u/TrySorn)\
**Post date:** [10 December 2020 14:57 UTC](https://discourse.nodered.org/t/how-to-get-date-time-to-mysql/37299/4 "2020-12-10T14:57:38Z")

</div>

> [@Steve-Mcl](#):
>
> ```auto
> 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.  
 ![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/d/8/d836121cb1d7b1d77b929ec38154ba3e87199414.png)  
i change something it show like this

```auto
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;

```

![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/9/5/952c032fd4b5ca05e861a2069d9f658c7bf51a90.png)

finally i had smae error to mysql  
 ![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/1/8/18bb6750e8d386e0c6fe720e1bd392c6f6edcc38.png)

---

<div class="post-metadata">

**Author:** ![Steve-Mcl](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/steve-mcl/32/4826_2.png) [@Steve-Mcl](https://discourse.nodered.org/u/Steve-Mcl)\
**Post date:** [10 December 2020 15:00 UTC](https://discourse.nodered.org/t/how-to-get-date-time-to-mysql/37299/5 "2020-12-10T15:00:34Z")

</div>

> [@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.

---

<div class="post-metadata">

**Author:** ![TrySorn](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/trysorn/32/33389_2.png) [@TrySorn](https://discourse.nodered.org/u/TrySorn)\
**Post date:** [10 December 2020 15:01 UTC](https://discourse.nodered.org/t/how-to-get-date-time-to-mysql/37299/6 "2020-12-10T15:01:24Z")

</div>

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  
 ![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/1/8/18bb6750e8d386e0c6fe720e1bd392c6f6edcc38.png)  
this is my function node code

```auto
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

 ![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/0/c/0cbac9594c5eb42fb34c1c90bbb552fabcb0e0c7.png)

---

<div class="post-metadata">

**Author:** ![Colin](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/colin/32/17040_2.png) [@Colin](https://discourse.nodered.org/u/Colin)\
**Post date:** [10 December 2020 15:09 UTC](https://discourse.nodered.org/t/how-to-get-date-time-to-mysql/37299/7 "2020-12-10T15:09:27Z")

</div>

> [@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.

---

<div class="post-metadata">

**Author:** ![TrySorn](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/trysorn/32/33389_2.png) [@TrySorn](https://discourse.nodered.org/u/TrySorn)\
**Post date:** [10 December 2020 15:15 UTC](https://discourse.nodered.org/t/how-to-get-date-time-to-mysql/37299/8 "2020-12-10T15:15:51Z")

</div>

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

 ![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/8/4/847763eebc02c5551ff1729b82d1e292f672cffa.png)  
Now my time must show like this 2020-12-10 22.14.30 what mean of T and Z

---

<div class="post-metadata">

**Author:** ![Steve-Mcl](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/steve-mcl/32/4826_2.png) [@Steve-Mcl](https://discourse.nodered.org/u/Steve-Mcl)\
**Post date:** [10 December 2020 15:25 UTC](https://discourse.nodered.org/t/how-to-get-date-time-to-mysql/37299/9 "2020-12-10T15:25:08Z")

</div>

> [@TrySorn](#):
>
> what mean of T and Z

Z = ZULU ([info](https://whatis.techtarget.com/definition/Zulu-Zulu-time))

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)

---

<div class="post-metadata">

**Author:** ![TrySorn](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/trysorn/32/33389_2.png) [@TrySorn](https://discourse.nodered.org/u/TrySorn)\
**Post date:** [10 December 2020 15:41 UTC](https://discourse.nodered.org/t/how-to-get-date-time-to-mysql/37299/10 "2020-12-10T15:41:07Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![TrySorn](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/trysorn/32/33389_2.png) [@TrySorn](https://discourse.nodered.org/u/TrySorn)\
**Post date:** [10 December 2020 16:02 UTC](https://discourse.nodered.org/t/how-to-get-date-time-to-mysql/37299/11 "2020-12-10T16:02:32Z")

</div>

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

```auto
  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.

 ![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/7/9/795e13e65ac7a393b652b54cc22b5d206e0fdacb.png)  
can i show only 2020-12-10 22:58:22

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/1X/d073cd938eafa2e558d7c2cd59003b3ef4963033.png) [@system](https://discourse.nodered.org/u/system)\
**Post date:** [8 February 2021 16:02 UTC](https://discourse.nodered.org/t/how-to-get-date-time-to-mysql/37299/12 "2021-02-08T16:02:33Z")

</div>

This topic was automatically closed 60 days after the last reply. New replies are no longer allowed.