type - Inserción de MySQL en DATETIME: ¿es seguro utilizar el formato ISO:: 8601?

string column mysql

Question

En nuestro proyecto, usamos el generador Zend Framework Model, que produce algo como esto para establecer las propiedades que se almacenan en DB (MySQL) como campos DATETIME:

public function setObjectDatetime($data) {
  if (! $data instanceof Zend_Date) { ... some conversion code ... }
  $this->objectDatetime = $data->toString(Zend_Date::ISO_8601);
}

Así que la cadena con formato ISO :: 8601 (''2012-06-15T18: 33: 00 + 03: 00'' por ejemplo) es lo que realmente se almacena como una propiedad.

El problema surge cuando intentamos save este modelo y pasar esta cadena a MySQL (versión 5.5.16): genera una advertencia, pero aún inserta / actualiza la fila correspondiente con un resultado correcto. Es fácil comprobar que el problema está causado por MySQL y no por el comportamiento de algunos controladores: simplemente emita la consulta como ...

UPDATE table_name SET datetime_field = ''2012-06-15T18:33:00+03:00'' WHERE id = 1;

... y el resultado será 1 row affected, 1 warning , con

1264 | Out of range value for column ''dt'' at row 1

advertencia (mostrada por SHOW WARNINGS ).

Para mi sorpresa, phpMyAdmin no muestra ninguna advertencia en absoluto; y todo el código del lado del servidor procesó esta consulta como sólida. )

Entonces, la pregunta es: ¿deberíamos realmente reformatear lo que almacenamos en nuestro Modelo en otro formato de cadena (''YY-MM-dd HH: mm: ss'', por ejemplo?) ¿O es solo un comportamiento extraño de MySQL que se solucionará? ¿tarde o temprano?

Answer 1

Parece que la respuesta corta a esta pregunta es "No, no es seguro", esta conclusión sigue a una serie de experimentos con el shell de MySQL. Aún así agradecería una respuesta más "teórica", aunque ...

Al parecer, el motor MySQL es (por defecto) bastante liberal en lo que acepta como un literal de fecha y sql_mode incluso con sql_mode establecido en STRICT_ALL_TABLES: no solo se aceptan varios separadores, también pueden diferir:

INSERT INTO t(dt) VALUES(''2012-01,03.04:05@06''); -- Query OK, 1 row affected

Además, si la cadena es demasiado corta, se rellenará con ceros ... pero puede haber sorpresas:

INSERT INTO t(dt) VALUES(''2012011''); -- 2020-12-01 01:00:00 is what''s inserted

Lo triste es que la cadena es demasiado larga (cuando el último dígito analizable va seguido de algo que no sea el espacio en blanco) se considerará un valor no válido en modo estricto:

mysql> INSERT INTO t(dt) VALUES(''2012-06-27T05:25Z'');
ERROR 1292 (22007): Incorrect datetime value: ''2012-06-27T05:25Z'' for column ''dt'' at row 1
mysql> INSERT INTO t(dt) VALUES(''2012-06-27T05:25'');
Query OK, 1 row affected (0.10 sec)

En el modo tradicional, el análisis es aún más relajado, pero no más preciso; además, las cadenas que se consideran incorrectas en el modo estricto darán una especie de "advertencias silenciosas", aunque las operaciones tendrán éxito:

mysql> INSERT INTO t(dt) VALUES(''2012-06-27T05:25Z'');
Query OK, 1 row affected, 1 warning (0.10 sec)

mysql> SHOW WARNINGS;
+---------+------+---------------------------------------------+
| Warning | 1264 | Out of range value for column ''dt'' at row 1 |
+---------+------+---------------------------------------------+

mysql> SELECT dt FROM t;
+---------------------+
| dt                  |
+---------------------+
| 2012-06-27 05:25:00 |
+---------------------+

La conclusión es que tuvimos que volver a escribir algunos códigos relacionados con DAL para que las fechas (y los tiempos de las fechas) siempre se envíen al DB en forma "normalizada". Me pregunto por qué somos nosotros los que tenemos que hacerlo, y no los desarrolladores de Zend_Db. Pero esa es otra historia, supongo. )

Answer 2

Por defecto, MySQL usa el formato ISO9075 para datetime

Los valores posibles para el primer y segundo argumento dan como resultado varias cadenas de formato posibles (para los especificadores utilizados, consulte la tabla en la descripción de la función DATE_FORMAT ()). El formato ISO se refiere a ISO 9075, no a ISO 8601.

http://dev.mysql.com/doc/refman/5.7/en/date-and-time-functions.html#function_get-format

Answer 3

Por lo que sé, no hay manera de almacenar la información de compensación de tiempo (la +03: 00 al final de su cadena ISO 8601) en los tipos de Fecha o Hora de MySQL, por lo que puede buscar una solución por su cuenta.

Un posible enfoque es dividir la cadena ISO 8601 y almacenar el desplazamiento en una columna char (5), aunque hay que admitir que sería difícil trabajar con él. Supongo que podría almacenar el desplazamiento en una columna de tiempo, lo que podría facilitar un poco las manipulaciones de fecha / hora.

EDITAR
Me topé con this en la documentación de MySQL, lo que puede ser útil.