# Node-red to mysql - incorrect datetime value
**URL:** https://discourse.nodered.org/t/node-red-to-mysql-incorrect-datetime-value/21344
**Category:** General
**Tags:** database
**Created:** [6 February 2020 10:13 UTC](https://discourse.nodered.org/t/node-red-to-mysql-incorrect-datetime-value/21344 "2020-02-06T10:13:39Z")
**Posts on this page:** 13
**Page:** 1
### Author:  [@freiheit2018](https://discourse.nodered.org/u/freiheit2018)
#### Post date: [6 February 2020 10:13 UTC](https://discourse.nodered.org/t/node-red-to-mysql-incorrect-datetime-value/21344/1 "2020-02-06T10:13:39Z")
Hello there, I am new to Node-Red but I like the idea and possiblilities very much. With my very first project, I tried to implement the "Log MQTT to MySQL" flow ([Log MQTT to MySQL (flow) - Node-RED](https://flows.nodered.org/flow/59fe2502dd82ae9b8a55b949a48e3d89))
But I haven´t get it to work so far, I keep getting this error code 🙂
"Error: ER\_TRUNCATED\_WRONG\_VALUE: Incorrect datetime value: '2020-02-06T09:55:33.184Z' for column 'timestamp' at row 1"
I have been looking all over the place to find a solution, it seems, that a lot of people have the same problem, but the simple answer, how to change the ISO-timestamp to something, that MySQL accepts, did not show up (or did I simply not find).
Which function can I use instead of
out = out + "VALUES ('" + new Date().toISOString() + "','"
I appreciate any help,
Jürgen
---
### Author:  [@Paul-Reed](https://discourse.nodered.org/u/Paul-Reed)
#### Post date: [6 February 2020 10:41 UTC](https://discourse.nodered.org/t/node-red-to-mysql-incorrect-datetime-value/21344/2 "2020-02-06T10:41:23Z")
Please search the forum, it has been answered here many times previously...
---
### Author:  [@freiheit2018](https://discourse.nodered.org/u/freiheit2018)
#### Post date: [6 February 2020 10:44 UTC](https://discourse.nodered.org/t/node-red-to-mysql-incorrect-datetime-value/21344/3 "2020-02-06T10:44:54Z")
Hi Paul, thank you for your reply, I was hoping for such an answer. Have a great day !
---
### Author:  [@Paul-Reed](https://discourse.nodered.org/u/Paul-Reed)
#### Post date: [6 February 2020 10:47 UTC](https://discourse.nodered.org/t/node-red-to-mysql-incorrect-datetime-value/21344/4 "2020-02-06T10:47:23Z")
Try searching for;
ER\_TRUNCATED\_WRONG\_VALUE
---
### Author:  [@freiheit2018](https://discourse.nodered.org/u/freiheit2018)
#### Post date: [6 February 2020 11:05 UTC](https://discourse.nodered.org/t/node-red-to-mysql-incorrect-datetime-value/21344/5 "2020-02-06T11:05:10Z")
Hi Paul, I found what I was looking for, but not in the forum! Thanks again!
---
### Author:  [@dceejay](https://discourse.nodered.org/u/dceejay)
#### Post date: [6 February 2020 11:27 UTC](https://discourse.nodered.org/t/node-red-to-mysql-incorrect-datetime-value/21344/6 "2020-02-06T11:27:25Z")
Feel free to add a link here - ready for next time .
---
### Author:  [@freiheit2018](https://discourse.nodered.org/u/freiheit2018)
#### Post date: [6 February 2020 11:57 UTC](https://discourse.nodered.org/t/node-red-to-mysql-incorrect-datetime-value/21344/7 "2020-02-06T11:57:51Z")
Hi Jay (I guess, that is your name),
I found a table, where the javascript date/time methods are described, I then changed the method from ISOString to toLocaleDateString(), but now, my table shows only the date and not the time. What I find confusing, is, that I followed the instructions on how to insert MQTT data into MySQL step by step, set up the database accordingly and still get error codes (apparently not only me).
---
### Author:  [@Paul-Reed](https://discourse.nodered.org/u/Paul-Reed)
#### Post date: [6 February 2020 14:12 UTC](https://discourse.nodered.org/t/node-red-to-mysql-incorrect-datetime-value/21344/8 "2020-02-06T14:12:34Z")
> [@Paul-Reed](#):
>
> Try searching for;
> ER\_TRUNCATED\_WRONG\_VALUE
Did you look in this forum?
---
### Author:  [@TotallyInformation](https://discourse.nodered.org/u/TotallyInformation)
#### Post date: [6 February 2020 18:15 UTC](https://discourse.nodered.org/t/node-red-to-mysql-incorrect-datetime-value/21344/9 "2020-02-06T18:15:45Z")
The details for how to use date/time with MySQL are in the excellent online manual:
[https://dev.mysql.com/doc/refman/8.0/en/date-and-time-types.html](https://dev.mysql.com/doc/refman/8.0/en/date-and-time-types.html)
It does not accept an ISO formatted date string. However, it would appear trivial to replace the "T" with a space and to remove the trailing "Z".
Haven't used MySQL for years I'm afraid so I can't test it.
---
### Author:  [@freiheit2018](https://discourse.nodered.org/u/freiheit2018)
#### Post date: [7 February 2020 07:39 UTC](https://discourse.nodered.org/t/node-red-to-mysql-incorrect-datetime-value/21344/10 "2020-02-07T07:39:04Z")
Hello Julian, thank you for your information. I was wondering, the node, I was using, is described as "Log MQTT to MySQL" and the Javascript in there does put out ISO-formatted timestamp. That is most probably the reason, why the search "Error: ER\_TRUNCATED\_WRONG\_VALUE is all over the place, many of them using the exact same node as I did. So, I finally fixed it with just letting the database add the timestamp, that´s it.
Have a great day, regards, Jürgen
---
### Author:  [@freiheit2018](https://discourse.nodered.org/u/freiheit2018)
#### Post date: [7 February 2020 07:40 UTC](https://discourse.nodered.org/t/node-red-to-mysql-incorrect-datetime-value/21344/11 "2020-02-07T07:40:16Z")
For one and a half day, the solution found is not satisfying, but, as I have mentioned, I found a workaround elsewhere. Have a nice day, regards, Jürgen
---
### Author:  [@TotallyInformation](https://discourse.nodered.org/u/TotallyInformation)
#### Post date: [7 February 2020 12:10 UTC](https://discourse.nodered.org/t/node-red-to-mysql-incorrect-datetime-value/21344/12 "2020-02-07T12:10:57Z")
I see from a quick search that friend of the forum @hardillb answered a Stack Overflow question some time back and part of his suggestions were using a different MQTT broker that supports a more flexible approach to storage such as HiveMQ.
>
With that, it might be possible to get the MQ service to do the heavy lifting if all you need is for MQTT messages to be replicated to a database.
Either way, yes, it is nearly always better to let the database engine do as much of the work as possible rather than trying to pass in information. Also worth looking at prepared statements if you are needing to pass lots of data into a db quickly. They mean that the db engine has less work to do and so your writes are faster.
---
### Author:  [@system](https://discourse.nodered.org/u/system)
#### Post date: [7 April 2020 12:11 UTC](https://discourse.nodered.org/t/node-red-to-mysql-incorrect-datetime-value/21344/13 "2020-04-07T12:11:07Z")
This topic was automatically closed 60 days after the last reply. New replies are no longer allowed.