MySQL insert to DATETIME: безопасно ли использовать формат ISO:: 8601?

В нашем проекте мы используем генератор моделей Zend Framework, который производит что-то вроде этого, чтобы задать свойства, которые хранятся в БД (MySQL) в виде полей DATETIME: 

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

Таким образом, форматированная строка ISO::8601 (например, '2012-06-15T18:33:00+03:00') - это то, что на самом деле хранится как свойство.

Проблема возникает, когда мы пытаемся save Эту модель и передаем эту строку в MySQL (версия 5.5.16): он вызывает предупреждение, но все равно вставляет / обновляет соответствующую строку с помощью правильный результат. Легко проверить, что проблема вызвана MySQL,а не поведением некоторых драйверов: просто выдайте такой запрос, как...

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

... и результат будет 1 row affected, 1 warning, с

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

Предупреждение (показано SHOW WARNINGS).

К моему удивлению, phpMyAdmin не показывает никаких предупреждений вообще; и весь серверный код обработал этот запрос как твердый. )

Таким образом, вопрос заключается в следующем: действительно ли мы должны переформатировать то, что мы храним в нашей модели, в другой строковый формат (Например, "YY-MM-dd HH: mm: ss"?) Или это просто какое-то странное поведение MySQL, которое рано или поздно будет исправлено?

3 16
mysql iso8601

3 ответа:

Похоже, что короткий ответ на этот вопрос - "нет, это небезопасно" - этот вывод следует из серии экспериментов с оболочкой MySQL. Тем не менее, я был бы признателен за более "теоретический" ответ...

По-видимому, движок MySQL (по умолчанию) довольно либеральен в том, что он принимает в качестве литерала Datetime даже с sql_mode, установленным в STRICT_ALL_TABLES : не только различные разделители принимаются, они также могут отличаться:

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

Кроме того, если строка слишком коротка, она будет будьте дополнены нулями... но могут быть и сюрпризы:

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

Печально, что слишком длинная строка (когда за последней разбираемой цифрой следует что-то другое, кроме пробела) будет считаться недопустимым значением в строгом режиме: 

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)

В традиционном режиме синтаксический анализ еще более расслаблен - но не более точен; кроме того, строки, которые считаются неправильными в строгом режиме, дадут своего рода "молчаливые предупреждения", хотя операции будут преуспеть: 

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 |
+---------------------+
Суть в том, что нам пришлось переписать некоторый код, связанный с DAL, чтобы даты (и время) всегда отправлялись в БД в "нормализованном" виде. Интересно, почему это должны делать мы, а не разработчики Zend_Db. Но это уже другая история, я полагаю. )
15

Насколько я знаю, нет способа хранить информацию о смещении времени (+03: 00 в конце строки ISO 8601) в типах даты или времени MySQL, поэтому вы как бы сами находите решение.

Один из возможных подходов состоит в том, чтобы разделить строку ISO 8601 и сохранить смещение в столбце char(5), хотя, по общему признанию, это затруднило бы работу с ней. Я полагаю, что вы могли бы хранить смещение в столбце времени, что может немного облегчить манипуляции с датой / временем легче.

EDIT
Я просто наткнулся на это в документах MySQL, которые могут быть полезны.

4

По умолчанию MySQL использует формат ISO9075 для datetime

Возможные значения для первого и второго аргументов приводят к нескольким возможным строкам формата (для используемых спецификаторов см. таблицу в описании функции DATE_FORMAT ()). Формат ISO относится к ISO 9075, а не ISO 8601.

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

2