0

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

4
  • Show us an example of the text that comes from the PLC. It is probably not compatible with DATETIME, but can be converted with an @variable and a function call.
    – Rick James
    May 5 at 0:56
  • I get '2021-04-30 08:51:30' from the PLC
    – E.Aigle
    May 5 at 5:09
  • Please provide SHOW CREATE TABLE, the LOAD statement, and a sample line from the file.
    – Rick James
    May 5 at 5:32
  • I'm not using a load statement. I'm using KepSerververEx and the Data logger plugin which writes in the base without me doing anything. There isn't a file I'm loading, it's literally just the date right now.
    – E.Aigle
    May 6 at 6:03
2

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.