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.
