1

I'm experiencing an issue of type compatibility in MySQL version 5.6. I have this data formatted as Local Date time (2007-01-01T00:25:47) and I need to write them on database, but I cannot find a corresponding type compatible with MySQL.

I have already tried with:

  • DATETIME
  • TIMESTAMP

Any suggestions?

UgoL
  • 839
  • 2
  • 13
  • 37

2 Answers2

2

Your problem is that the T in your Local Date time makes the value incompatible with MySQL's DATETIME or TIMESTAMP columns. However you can work around that using either STR_TO_DATE to convert the format, or using REPLACE to replace the T with a space (thus making it a MySQL compatible datetime). For example:

create table test (d datetime, t timestamp);
insert into test values 
(str_to_date('2007-01-01T00:25:47', '%Y-%m-%dT%H:%i:%s'), str_to_date('2007-01-01T00:25:47', '%Y-%m-%dT%H:%i:%s')),
(replace('2007-01-01T00:25:47', 'T', ' '),replace('2007-01-01T00:25:47', 'T', ' '));
select * from test

Output:

d                       t
2007-01-01 00:25:47     2007-01-01 00:25:47
2007-01-01 00:25:47     2007-01-01 00:25:47

Demo on dbfiddle

Nick
  • 138,499
  • 22
  • 57
  • 95
0

Have you tried using the MySQL command for parsing a string to a date?

STR_TO_DATE(str,format)

You could try converting your current type to a string, then pipe it though the STR_TO_DATE command to then be stored on your database in the correct format.

https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html#function_str-to-date

James m
  • 144
  • 12
  • Bu there is now way for import the the type field "Local datetime" in MySQL? – UgoL May 16 '19 at 11:03
  • I'm looking through the MySQL docs and there doesn't appear to be a "LocalDateTime" data type to store, however I found this link which could potentially be of use? https://stackoverflow.com/questions/19843203/how-to-store-a-datetime-in-mysql-with-timezone-info Apologies if it it isn't, I'm not to sure what your use case is but if it doesn't work please let me know and I'll try to help further. – James m May 16 '19 at 11:09