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)
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 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).
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.
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
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.
