10.3.14 Indexed Lookups from TIMESTAMP Columns — MySQL 9.2

10.3.14 Индексированные запросы по столбцам TIMESTAMP

Временные значения хранятся в столбцах TIMESTAMP в качестве значений UTC, а значения, вставленные в и извлеченные из столбцов TIMESTAMP, преобразуются между часовым поясом сессии и UTC. (Это тот же тип преобразования, что выполняет функция CONVERT_TZ(). Если часовой пояс сессии UTC, то преобразования часовых поясов фактически не происходит.)

Из-за правил изменения часового пояса, таких как летнее время (DST), преобразования между UTC и часовыми поясами, отличными от UTC, не являются взаимно однозначными в обоих направлениях. Отличные значения UTC могут быть неотличимы в другом часовом поясе. Следующий пример показывает отличные значения UTC, которые становятся идентичными в часовом поясе, отличном от UTC:

mysql> CREATE TABLE tstable (ts TIMESTAMP);
mysql> SET time_zone = 'UTC'; -- insert UTC values
mysql> INSERT INTO tstable VALUES
       ('2018-10-28 00:30:00'),
       ('2018-10-28 01:30:00');
mysql> SELECT ts FROM tstable;
+---------------------+
| ts                  |
+---------------------+
| 2018-10-28 00:30:00 |
| 2018-10-28 01:30:00 |
+---------------------+
mysql> SET time_zone = 'MET'; -- retrieve non-UTC values
mysql> SELECT ts FROM tstable;
+---------------------+
| ts                  |
+---------------------+
| 2018-10-28 02:30:00 |
| 2018-10-28 02:30:00 |
+---------------------+

Примечание

Для использования именованных часовых поясов, таких как 'MET' или 'Europe/Amsterdam', таблицы часовых поясов должны быть правильно настроены. Инструкции см. в Разделе 7.1.15, «Поддержка часовых поясов MySQL Server».

Вы можете видеть, что два отличных значения UTC совпадают при преобразовании в часовой пояс 'MET'. Это явление может привести к разным результатам для запроса столбца TIMESTAMP, в зависимости от того, использует ли оптимизатор индекс для выполнения запроса.

Предположим, что запрос выбирает значения из таблицы, показанной ранее, используя условие WHERE для поиска столбца ts по одному конкретному значению, такому как литерал timestamp, предоставленный пользователем:

SELECT ts FROM tstable
WHERE ts = 'literal';

Предположим далее, что запрос выполняется при этих условиях:

Часовой пояс сессии не UTC и имеет сдвиг DST. Например:
```
SET time_zone = 'MET';
```
Уникальные значения UTC, хранящиеся в столбце TIMESTAMP, не являются уникальными в часовом поясе сессии из-за сдвигов DST. (Показанный ранее пример иллюстрирует, как это может произойти.)
В запросе указано значение поиска, которое находится в течение часа перехода на летнее время в часовом поясе сессии.

При этих условиях сравнение в условии WHERE происходит по-разному для запросов без индекса и с индексом и приводит к разным результатам:

Если индекса нет или оптимизатор не может его использовать, сравнения выполняются в часовом поясе сессии. Оптимизатор выполняет сканирование таблицы, в котором он извлекает каждое значение столбца ts, преобразует его из UTC в часовой пояс сессии и сравнивает его со значением поиска (также интерпретируется в часовом поясе сессии):
```
mysql> SELECT ts FROM tstable
       WHERE ts = '2018-10-28 02:30:00';
+---------------------+
| ts                  |
+---------------------+
| 2018-10-28 02:30:00 |
| 2018-10-28 02:30:00 |
+---------------------+
```
Поскольку сохранённые значения ts преобразуются в часовой пояс сессии, запрос может вернуть два значения timestamp, которые отличны как значения UTC, но равны в часовом поясе сессии: одно значение, которое появляется до сдвига DST, когда часы меняются, и одно значение, которое появляется после сдвига DST.
Если есть используемый индекс, сравнения выполняются в UTC. Оптимизатор выполняет сканирование индекса, сначала преобразуя значение поиска из часового пояса сессии в UTC, а затем сравнивает результат со записями индекса UTC:
```
mysql> ALTER TABLE tstable ADD INDEX (ts);
mysql> SELECT ts FROM tstable
       WHERE ts = '2018-10-28 02:30:00';
+---------------------+
| ts                  |
+---------------------+
| 2018-10-28 02:30:00 |
+---------------------+
```
В этом случае значение поиска (после преобразования) соответствует только записям индекса, и поскольку записи индекса для различных сохранённых значений UTC также отличны, значение поиска может соответствовать только одной из них.

Из-за различной работы оптимизатора для запросов без индекса и с индексом, запрос даёт разные результаты в каждом случае. Результат запроса без индекса возвращает все значения, которые соответствуют в часовом поясе сессии. Индексированный запрос не может этого сделать:

Он выполняется внутри хранилища данных, которое знает только о значениях UTC.
Для двух различных значений часового пояса сессии, которые отображаются на одно и то же значение UTC, индексированный запрос соответствует только соответствующей записи индекса UTC и возвращает только одну строку.

В предыдущем обсуждении набор данных, хранящийся в tstable, случайно состоит из отличных значений UTC. В таких случаях все запросы, использующие индекс, вида, показанного выше, соответствуют не более чем одной записи индекса.

Если индекс не UNIQUE, то в таблице (и индексе) возможно хранение нескольких экземпляров данного значения UTC. Например, столбец ts может содержать несколько экземпляров значения UTC '2018-10-28 00:30:00'. В этом случае запрос, использующий индекс, вернул бы все из них (преобразованные в значение MET '2018-10-28 02:30:00' в наборе результатов). Остаётся верным, что запросы, использующие индекс, сопоставляют преобразованное значение поиска с одним значением в записях индекса UTC, а не с множеством значений UTC, которые преобразуются в значение поиска в часовом поясе сессии.

Если важно вернуть все значения ts, которые совпадают в часовом поясе сессии, обходным путём является подавление использования индекса с подсказкой IGNORE INDEX:

mysql> SELECT ts FROM tstable
       IGNORE INDEX (ts)
       WHERE ts = '2018-10-28 02:30:00';
+---------------------+
| ts                  |
+---------------------+
| 2018-10-28 02:30:00 |
| 2018-10-28 02:30:00 |
+---------------------+

Та же проблема несовпадения для преобразований часовых поясов в обоих направлениях возникает и в других контекстах, таких как преобразования, выполняемые с функциями FROM_UNIXTIME() и UNIX_TIMESTAMP(). См. Раздел 14.7, «Функции даты и времени».