8.3.11 Indexed Lookups from TIMESTAMP Columns — MySQL 5.7

8.3.11 Индексированные запросы к столбцам 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', необходимо правильно настроить таблицы часовых поясов. Инструкции см. в разделе 5.1.13, «Поддержка часовых поясов сервера MySQL».

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

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

SELECT ts FROM tstable
WHERE ts = 'literal';

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

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

При этих условиях сравнение в условии 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 преобразуются в часовой пояс сессии, запрос может вернуть два значения времени, которые являются различными как значения UTC, но равными в часовом поясе сессии: одно значение, которое появляется до сдвига летнего времени при изменении часов, и одно значение, которое появляется после сдвига летнего времени.
Если индекс доступен, сравнения выполняются в 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(). См. раздел 12.7, «Функции даты и времени».