Incorrect datetime value Mysql

Question

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

Answer 1

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.

Answer 2

I ran into a similar problem when importing a Mysql Dump from a DB with a different timezone. That column was a TIMESTAMP that uses the CURRENT_TIMESTAMP as default value.

By doing a SELECT @@time_zone I saw that both servers were using SYSTEM as timezone. But by typying date on terminal, I saw that one server was using UTC and the other AEST.

---

Set your timezone before importing. Changing time zone for that session in MySQL:

I realized that we can set the same timezone just for that import session by doing

SET time_zone='+00:00' and run your import.

Setting global timezone in MySQL

Or you can set your mysql global timezone and don't need to care with that in the future (until you restart mysql).

SET @@global.time_zone='+00:00'

Setting in your my.cnf

You can do that in many different ways, by setting an init statement, or as described in the documentation, or even different if you're using MAMP. Check this answer for the proper way for doing this.