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

В нашем проекте мы используем генератор Zend Framework Model, который создает что-то вроде этого, чтобы установить свойства, которые хранятся в DB (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).

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

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

Ответы

Ответ 1

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

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

INSERT INTO t(dt) VALUES('2012-01,03.04:[email protected]'); -- Query OK, 1 row affected

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

INSERT INTO t(dt) VALUES('2012011'); -- 2020-12-01 01:00:00 is what 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, так что даты (и datetimes) всегда отправляются в БД в "нормализованной" форме. Интересно, почему это нам нужно, а не разработчикам Zend_Db. Но эта другая история, я полагаю. )

Ответ 2

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

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

ИЗМЕНИТЬ
Я просто наткнулся на этот в документах MySQL, что может быть полезно.

Ответ 3

По умолчанию 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