Invalid datetime when converting to timestamp

Hi there.
Today I faced an issue that from first looking at it I was almost sure I`ve found a bug, but putting a bit more research on it, it makes totally sense.

I had a table, that I`ll call here as t1. This table has a field that is datetime. Due to a normalization project that we are working on, we are changing some datetime fields to timestamp. When I tried to convert t1, I got the bellow error:

mysql> show create table t1;
+-------+-------------------------------------------------------------------------------------------+
| Table | Create Table                                                                              |
+-------+-------------------------------------------------------------------------------------------+
| t1    | CREATE TABLE `t1` (
  `date` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-------+-------------------------------------------------------------------------------------------+
1 row in set (0,00 sec)

mysql> select * from t1;
+---------------------+
| date                |
+---------------------+
| 2010-03-28 01:28:20 |
+---------------------+
1 row in set (0,00 sec)

mysql> alter table t1 modify date timestamp not null default current_timestamp;
ERROR 1292 (22007): Incorrect datetime value: '2010-03-28 01:28:20' for column 'date' at row 1

This date looked perfectly fine. Then I realized that we use Europe/Dublin as timezone, I went to http://www.timeanddate.com/time/change/ireland?year=2010 to checkout when Daylight Savings started on 2010 in Ireland, and guess what ?!?!?!
Bingo, that is exactly the day DST started on my server timezone. From the above website:

When local standard time was about to reach
Sunday, 28 March 2010, 01:00:00 clocks were turned forward 1 hour to
Sunday, 28 March 2010, 02:00:00 local daylight time instead

Which means 01:28:20 never existed on Europe/Dublin for that specific day. Why the data was there in the first place is another discussion. But why it did not complained before ?
FROM: http://dev.mysql.com/doc/refman/5.6/en/datetime.html

MySQL converts TIMESTAMP values from the current time zone to UTC for storage, and back from UTC to the current time zone for retrieval. (This does not occur for other types such as DATETIME.)

So, how to fix it ?
Identify the records that have an invalid date and fix it(In my case, fix it means add an hour to it`s time). One way to do it is using the bellow approach:

mysql> SELECT date FROM t1 WHERE FROM_UNIXTIME(UNIX_TIMESTAMP(date)) <> date;
+---------------------+
| date |
+---------------------+
| 2010-03-28 01:28:20 |
+---------------------+
1 row in set (0,00 sec)

mysql> UPDATE t1 SET date=DATE_ADD(date, INTERVAL 1 hour) WHERE FROM_UNIXTIME(UNIX_TIMESTAMP(date)) <> date;
Query OK, 1 row affected (0,10 sec)
Rows matched: 1 Changed: 1 Warnings: 0

mysql> SELECT date FROM t1 WHERE FROM_UNIXTIME(UNIX_TIMESTAMP(date)) <> date;
Empty set (0,00 sec)

mysql> alter table t1 modify date timestamp not null default current_timestamp;
Query OK, 1 row affected (0,05 sec)
Records: 1 Duplicates: 0 Warnings: 0

mysql> SELECT * FROM t1;
+---------------------+
| date |
+---------------------+
| 2010-03-28 02:28:20 |
+---------------------+
1 row in set (0,00 sec)

We have identified all records, fixed it and then mysql allowed us to convert it to Timestamp.

That is it for today.

Liked? Help and Share!
This entry was posted in MySQL. Bookmark the permalink.
  • Brandon Liles

    I recommend setting the server TZ to UTC and then rely on client app code to convert to local time to avoid strangeness like this and support i18n.