Er_truncated_wrong_value

General
1

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

2

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

3

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

4

In your code you set the date to:

MYSQL wants a different format.

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

[{"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"}]
5

Thanks for your help bakman that has worked!

6

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