Search code examples
mysqldatedatetimetimezonedst

What's wrong with '2018-03-22 00:00:00" in MySQL?

I want to update a date field and set it to 2018-03-22 00:00:00 but I get the following stupid error:

Error Code: 1292. Incorrect datetime value: '2018-03-22 00:00:00' for column 'Date' at row 158917

This is the query I use for updating:

update assets.transactions 
set date = date_add(date, interval 1 hour)
where date between '2018-03-21 23:00:00' and '2018-06-29 23:59:59';

What is wrong? I searched a lot and found out dates before 1970-01-01 00:00:01 are not supported by MySQL, that is acceptable, but a date in the middle of 2018? That's something I can't digest.

Is there any solution to make this work right?

Solution

  • I guess you're updating a TIMESTAMP column. I also guess you have your MySQL instance set to a timezone with a daylight time switchover on 23-March-2018. I guess the rules of your timezone switchover in your country mean that the clock rolls over from 21-March-2018 11:59:59 to 22-March-2018 01:00:00.

    So the value 2018-03-22 00:00:00 just doesn't exist.

    Strange, isn't it?

    Try issuing this MySQL command, to set the time zone to UTC, before doing these sorts of mass timestamp updates. 

    SET time_zone = 'UTC';

    Don't forget to switch it back before doing other operations. Or just do those operations from a different MySQL connection.