Incorrect datetime value – convertendo para timestamp

Fala pessoal.
Hoje eu passei por um problema que em um primeiro momento parecia claramente um bug. Mas olhando um pouco melhor para ele, fez todo sentido.

Eu tenho uma tabela, que aqui vou chamar de t1. Essa tabela tem um campo usando datetime. Durante um projeto de normalização do banco, estamos trocando alguns campos de datetime para timestamp.
Quando eu tentei converter a tabela eu recebi o seguinte erro:

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

Olhando o formato da data, ela parece uma data valida. Depois de um tempo me dei por conta de que nosso timezone é Europe/Dublin. Quando fui pesquisar quando que o horario de verão entrou no ano de 2010, adivinha o que eu achei ???

Isso mesmo, esse foi exatamente o dia que o horario de verão entrou em Dublin naquele ano. Basicamente a 1 da manhã os relógios adiantaram uma hora. Isso que dizer que 01:28:20 nunca existiu no meu timezone. Por que esse valor estava no banco em primeiro lugar é outra discução. Mas por que o banco não reclamou sobre isso antes ?

Segundo a documentação: 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.)

MySQL converte campos TIMESTAMP do timezone original para UTC no caso de armazenamento, e converte de volta de UTC para o timezone do servidor no caso de consulta. (Isso não ocorre para outros tipos de campos, como DATETIME.)

Então, como arrumar ?
Primeiro vamos identificar as linhas que tem uma data invalida e arrumar elas (No meu caso, adicionar uma hora nela). Uma maneira de fazer isso é usando as queries abaixo:

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)

Identificamos as linhas, arrumamos elas e o MySQL permitiu a conversão para TIMESTAMP.

Por hoje é só.

Gostou ? Ajude e Compartilhe!
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.