I am trying to get data from a PLC directly into a MySQL 8.0 database (using MySQL Workbench). I need to know what data type the input value is for a column set to DATETIME so I can send in the right format.
Actual question: I assume it's string, because when I manually input values using and INSERT, the value has to be in ' '. Is this a stupid assumption on my part?
When the output of my PLC is a string and the datatype of my column is DATETIME, what I read from a SELECT in my DB is completely wrong : '1905-07-13 00:00:00' instead of '2021-04-30 08:51:30'. However, if I write it in the DB column set to be of type VARCHAR, it's written correctly, but not usable for my next app. So if I then change the column to DATETIME, everything works great, but that's super impractical and I can be changing the datatype of my column every time I need to add to the DB.
Another assumption I'm making here is that the input datatype is what's causing this problem, but as it is now, DATE and DATETIME are not supported by the driver I'm using, so maybe it's just wishful thinking on my part that any of this will work at all.
Note: Please do migrate this if it's off topic, wasn't really sure if it was a best fit here or stack overflow…
Second Note: The only way I have found to make this work is to write into the DB as a VARCHAR and then use CONVERT(,DATETIME) in my app, but it feels weird that I can't just write a date in my DB for some reason…..
Best Answer
You must provide the value as string literal or as a numeric literal.
Possible formats are described in MySQL 8.0 Reference Manual / ... / Date and Time Literals.
Alternatively you may provide the data in any custom format and apply STR_TO_DATE() to it in INSERT query with according parsing pattern.
DEMO