# Er\_truncated\_wrong\_value

**URL:** <https://discourse.nodered.org/t/er-truncated-wrong-value/39599>\
**Category:** General\
**Created:** [23 January 2021 11:29 UTC](https://discourse.nodered.org/t/er-truncated-wrong-value/39599 "2021-01-23T11:29:33Z")\
**Posts on this page:** 6\
**Page:** 1

<div class="post-metadata">

**Author:** ![rdcgreen](https://avatars.discourse-cdn.com/v4/letter/r/c57346/32.png) [@rdcgreen](https://discourse.nodered.org/u/rdcgreen)\
**Post date:** [23 January 2021 11:29 UTC](https://discourse.nodered.org/t/er-truncated-wrong-value/39599/1 "2021-01-23T11:29:34Z")

</div>

i'm trying to connect node-red to MySQL. I have the following error message come up:

"Error: ER\_TRUNCATED\_WRONG\_VALUE: Incorrect datetime value: '2021-01-23T11:22:13.645Z' for column 'timestamp' at row 1"

The column in the MySQL table is formatted as 'timestamp'

`[{"id":"f52c2237.0f41e","type":"tab","label":"GreenPoint EV","disabled":false,"info":""},{"id":"a06d74db.919808","type":"mqtt in","z":"f52c2237.0f41e","name":"kWh cumulative","topic":"emon/emonpi/use_kWh","qos":"2","datatype":"auto","broker":"407a01e4.6b637","x":190,"y":240,"wires":[["98201759.e4d558","dd93c350.95817"]]},{"id":"6825dd23.d4ee44","type":"debug","z":"f52c2237.0f41e","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","statusVal":"","statusType":"auto","x":660,"y":200,"wires":[]},{"id":"98201759.e4d558","type":"function","z":"f52c2237.0f41e","name":"Create query in topic","func":"var out = \"INSERT INTO Node_Red (timestamp,topic,data)\"\nout = out + \"VALUES ('\" + new Date().toISOString() + \"','\" \nout = out + msg.topic + \"','\" + msg.payload + \"');\"\n \nmsg.topic=out;\n\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","x":310,"y":340,"wires":[["6825dd23.d4ee44","ddb3e588.199508"]]},{"id":"4265dd53.5b18a4","type":"debug","z":"f52c2237.0f41e","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","statusVal":"","statusType":"auto","x":780,"y":400,"wires":[]},{"id":"ddb3e588.199508","type":"mysql","z":"f52c2237.0f41e","mydb":"13d305b5.48d5ba","name":"GreenPoint EV","x":550,"y":400,"wires":[["4265dd53.5b18a4"]]},{"id":"dd93c350.95817","type":"debug","z":"f52c2237.0f41e","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"topic","targetType":"msg","statusVal":"","statusType":"auto","x":340,"y":180,"wires":[]},{"id":"5c7304dd.c9e98c","type":"inject","z":"f52c2237.0f41e","name":"","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"","payloadType":"date","x":150,"y":80,"wires":[["29ec76d.ea02c8a"]]},{"id":"29ec76d.ea02c8a","type":"mqtt out","z":"f52c2237.0f41e","name":"kWh cumulative","topic":"emon/emonpi/use_kWh","qos":"","retain":"","broker":"407a01e4.6b637","x":410,"y":80,"wires":[]},{"id":"407a01e4.6b637","type":"mqtt-broker","name":"","broker":"localhost","port":"1883","clientid":"","usetls":false,"compatmode":true,"keepalive":"60","cleansession":true,"birthTopic":"","birthQos":"0","birthPayload":"","closeTopic":"","closePayload":"","willTopic":"","willQos":"0","willPayload":""},{"id":"13d305b5.48d5ba","type":"MySQLdatabase","name":"GreenPointEV","host":"51.145.7.36","port":"3306","db":"report_db","tz":"","charset":"UTF8"}]`

This is my code, that i have taken from another user showing how to connect a db

Many thanks

---

<div class="post-metadata">

**Author:** ![bakman2](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/bakman2/32/6207_2.png) [@bakman2](https://discourse.nodered.org/u/bakman2)\
**Post date:** [23 January 2021 11:39 UTC](https://discourse.nodered.org/t/er-truncated-wrong-value/39599/2 "2021-01-23T11:39:13Z")

</div>

> The **format** of a **TIMESTAMP** is YYYY-MM-DD HH:MM:SS which is fixed at 19 characters

---

<div class="post-metadata">

**Author:** ![rdcgreen](https://avatars.discourse-cdn.com/v4/letter/r/c57346/32.png) [@rdcgreen](https://discourse.nodered.org/u/rdcgreen)\
**Post date:** [23 January 2021 12:18 UTC](https://discourse.nodered.org/t/er-truncated-wrong-value/39599/3 "2021-01-23T12:18:25Z")

</div>

> [@rdcgreen](#):
>
> Incorrect datetime value: '2021-01-23T11:22:13.645Z'

Is this from MySQL then? I've tried 'Datetime' as the Type in the table

---

<div class="post-metadata">

**Author:** ![bakman2](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/bakman2/32/6207_2.png) [@bakman2](https://discourse.nodered.org/u/bakman2)\
**Post date:** [23 January 2021 12:36 UTC](https://discourse.nodered.org/t/er-truncated-wrong-value/39599/4 "2021-01-23T12:36:09Z")

</div>

In your code you set the date to:

> [@rdcgreen](#):
>
> new Date().toISOString()

MYSQL wants a different format.

You can try it like this. it uses a change node and sets the proper format.

```auto
[{"id":"a06d74db.919808","type":"mqtt in","z":"f52c2237.0f41e","name":"kWh cumulative","topic":"emon/emonpi/use_kWh","qos":"2","datatype":"auto","broker":"407a01e4.6b637","x":128,"y":168,"wires":[["6e2aa1c5.cc9a7"]]},{"id":"98201759.e4d558","type":"function","z":"f52c2237.0f41e","name":"Create query in topic","func":"var out = \"INSERT INTO Node_Red (timestamp,topic,data)\"\nout += \"VALUES ('\" + msg.mysqltimestamp + \"','\" \nout += msg.topic + \"','\" + msg.payload + \"');\"\n \nmsg.topic=out;\n\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","x":508,"y":168,"wires":[["ddb3e588.199508"]]},{"id":"4265dd53.5b18a4","type":"debug","z":"f52c2237.0f41e","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","statusVal":"","statusType":"auto","x":838,"y":240,"wires":[]},{"id":"ddb3e588.199508","type":"mysql","z":"f52c2237.0f41e","mydb":"13d305b5.48d5ba","name":"GreenPoint EV","x":728,"y":168,"wires":[["4265dd53.5b18a4"]]},{"id":"5c7304dd.c9e98c","type":"inject","z":"f52c2237.0f41e","name":"","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"","payloadType":"date","x":150,"y":80,"wires":[["29ec76d.ea02c8a"]]},{"id":"29ec76d.ea02c8a","type":"mqtt out","z":"f52c2237.0f41e","name":"kWh cumulative","topic":"emon/emonpi/use_kWh","qos":"","retain":"","broker":"407a01e4.6b637","x":410,"y":80,"wires":[]},{"id":"6e2aa1c5.cc9a7","type":"change","z":"f52c2237.0f41e","name":"timestamp","rules":[{"t":"set","p":"mysqltimestamp","pt":"msg","to":"$moment($now()).format(\"YYYY-MM-DD HH:mm:ss\")\t","tot":"jsonata"}],"action":"","property":"","from":"","to":"","reg":false,"x":310,"y":168,"wires":[["98201759.e4d558"]]},{"id":"407a01e4.6b637","type":"mqtt-broker","name":"","broker":"localhost","port":"1883","clientid":"","usetls":false,"compatmode":true,"keepalive":"60","cleansession":true,"birthTopic":"","birthQos":"0","birthPayload":"","closeTopic":"","closePayload":"","willTopic":"","willQos":"0","willPayload":""},{"id":"13d305b5.48d5ba","type":"MySQLdatabase","name":"GreenPointEV","host":"51.145.7.36","port":"3306","db":"report_db","tz":"","charset":"UTF8"}]

```

---

<div class="post-metadata">

**Author:** ![rdcgreen](https://avatars.discourse-cdn.com/v4/letter/r/c57346/32.png) [@rdcgreen](https://discourse.nodered.org/u/rdcgreen)\
**Post date:** [23 January 2021 14:03 UTC](https://discourse.nodered.org/t/er-truncated-wrong-value/39599/5 "2021-01-23T14:03:30Z")

</div>

> [@bakman2](#):
>
> ```auto
> [{"id":"a06d74db.919808","type":"mqtt in","z":"f52c2237.0f41e","name":"kWh cumulative","topic":"emon/emonpi/use_kWh","qos":"2","datatype":"auto","broker":"407a01e4.6b637","x":128,"y":168,"wires":[["6e2aa1c5.cc9a7"]]},{"id":"98201759.e4d558","type":"function","z":"f52c2237.0f41e","name":"Create query in topic","func":"var out = \"INSERT INTO Node_Red (timestamp,topic,data)\"\nout += \"VALUES ('\" + msg.mysqltimestamp + \"','\" \nout += msg.topic + \"','\" + msg.payload + \"');\"\n \nmsg.topic=out;\n\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","x":508,"y":168,"wires":[["ddb3e588.199508"]]},{"id":"4265dd53.5b18a4","type":"debug","z":"f52c2237.0f41e","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","statusVal":"","statusType":"auto","x":838,"y":240,"wires":[]},{"id":"ddb3e588.199508","type":"mysql","z":"f52c2237.0f41e","mydb":"13d305b5.48d5ba","name":"GreenPoint EV","x":728,"y":168,"wires":[["4265dd53.5b18a4"]]},{"id":"5c7304dd.c9e98c","type":"inject","z":"f52c2237.0f41e","name":"","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"","payloadType":"date","x":150,"y":80,"wires":[["29ec76d.ea02c8a"]]},{"id":"29ec76d.ea02c8a","type":"mqtt out","z":"f52c2237.0f41e","name":"kWh cumulative","topic":"emon/emonpi/use_kWh","qos":"","retain":"","broker":"407a01e4.6b637","x":410,"y":80,"wires":[]},{"id":"6e2aa1c5.cc9a7","type":"change","z":"f52c2237.0f41e","name":"timestamp","rules":[{"t":"set","p":"mysqltimestamp","pt":"msg","to":"$moment($now()).format(\"YYYY-MM-DD HH:mm:ss\")\t","tot":"jsonata"}],"action":"","property":"","from":"","to":"","reg":false,"x":310,"y":168,"wires":[["98201759.e4d558"]]},{"id":"407a01e4.6b637","type":"mqtt-broker","name":"","broker":"localhost","port":"1883","clientid":"","usetls":false,"compatmode":true,"keepalive":"60","cleansession":true,"birthTopic":"","birthQos":"0","birthPayload":"","closeTopic":"","closePayload":"","willTopic":"","willQos":"0","willPayload":""},{"id":"13d305b5.48d5ba","type":"MySQLdatabase","name":"GreenPointEV","host":"51.145.7.36","port":"3306","db":"report_db","tz":"","charset":"UTF8"}]
> 
> ```

Thanks for your help bakman that has worked!

---

<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:** [24 March 2021 14:03 UTC](https://discourse.nodered.org/t/er-truncated-wrong-value/39599/6 "2021-03-24T14:03:33Z")

</div>

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