2

I got MySql 5.7 DB on hosting. DB contains table with datetime column. for example:

CREATE TABLE test_date (
  `date` datetime NOT NULL
)

There is some date in PHP application which we should store into datetime column. To achieve that date is formatted using DateTime::DATE_ATOM format. PHP application is inserting record into that table using some framework (this is not important) so finally INSERT query is looking as the following:

INSERT INTO `test_date`(`date`) VALUES ('2018-05-22T12:33:16-03:00')

but MySql is throwing an error:

#1292 - Incorrect datetime value: '2018-05-22T12:33:16-03:00' for column 'date' at row 1

if I remove time zone offset from string (-03:00) insert is completed successfully.

I have multiple instances of MySql 5.6 installed on local VM and on different remote data centers but I never faced this issue: MySql always worked correctly with datetime formatted using DateTime::DATE_ATOM.

I digged Google but didn't find any specific changes in MySql 5.7 related to datetime.

Can you please point me why DateTime::DATE_ATOM string is rejected by MySql 5.7 or provide any pointers?

Update: looks like root cause of this issue is missed NO_ENGINE_SUBSTITUTION sqlMode (ref MYSQL incorrect DATETIME format). Looks like it is set by default on most of installations (or admins are setting it) but not on my hosting.

Alexey
  • 21
  • 3
  • 1
    https://dev.mysql.com/doc/refman/8.0/en/date-and-time-literals.html describes the syntax of `DATETIME` literals. I don't see anything about allowing a timezone offset. I suspect 5.6 was just ignoring this, and 5.7 has gotten more strict, or maybe you have different SQL modes set. – Barmar May 22 '18 at 19:19

0 Answers0