Mysql – Incorrect datetime value Mysql

datetimeMySQL

Good morning.
Mysql 5.7.25:

insert into mytable values('2019-03-31 02:06:29') 

return error "#1292 Incorrect datetime value"

but:

insert into mytable values('2019-03-31 03:06:29') 

return no errors.
(the type of the field is timestamp)

Where am I wrong ?

Thanks

Chico

Best Answer

If this gives you an error:

insert into mytable values('2019-03-31 02:06:29') 

But this doesn't:

insert into mytable values('2019-03-31 03:06:29')

You are most likely using a EU-like timezone, in particular, CET (I strongly suggest not to use tz on databases, and just use UTC, it is just easier). EU (and other countries) changed the clocks from 2 -> 3 am that Sunday for summertime adjustment, so it is not possible, if using such a TZ, to insert a '2019-03-31 02:XX:XX ' data, as such data is invalid for that timezone.

Do:

SELECT @@time_zone;

To know which timezone you are using. If it says SYSTEM, check it on the os.

Fix: Do not insert it, that clock time didn't exist, either it happened before 2 or after 3. Or fix your timezone of your database or os to match the one you are trying to insert.

Proper fix: Do not handle timezones on the database (never, ever try to implement then on your own), do it only at presentation time, and use UTC for your application backend. Set your servers also to use UTC.