DATETIME и TIMESTAMP

MySQL имеет два типа данных с датой и временем: DATETIME и TIMESTAMP. В официальной документации есть хорошее объяснение и примеры их различий: https://dev.mysql.com/doc/refman/8.0/en/datetime.html

Короче говоря, «MySQL преобразует TIMESTAMP значения из текущего часового пояса в UTC для хранения и обратно из UTC в текущий часовой пояс для извлечения. (Этого не происходит с другими типами, такими как DATETIME.) »

Другими словами, информация о часовом поясе теряется в столбцах DATETIME. Это может не быть большой проблемой, если ваши варианты использования в отношении часового пояса однородны, однако, если ваши пользователи находятся в разных часовых поясах, TIMESTAMP более эффективен в использовании.

Давайте посмотрим на несколько примеров:

mysql> CREATE TABLE time_test (timezone VARCHAR(32), time_datetime DATETIME, time_timestamp TIMESTAMP);
Query OK, 0 rows affected (0.02 sec)
mysql> SET time_zone='America/Los_Angeles';
Query OK, 0 rows affected (0.00 sec)
mysql> INSERT INTO time_test VALUES (@@SESSION.time_zone, '2020-03-12 11:00:00', '2020-03-12 11:00:00');
Query OK, 1 row affected (0.01 sec)
mysql> SELECT * FROM time_test;
+---------------------+---------------------+---------------------+
| timezone            | time_datetime       | time_timestamp      |
+---------------------+---------------------+---------------------+
| America/Los_Angeles | 2020-03-12 11:00:00 | 2020-03-12 11:00:00 |
+---------------------+---------------------+---------------------+
1 row in set (0.00 sec)

В приведенном выше примере

  • Часовой пояс сеанса: America / Los_Angeles
  • Попытка вставить время 11:00:00 в столбцы DATETIME и TIMESTAMP.
  • Результат выглядит отлично в текущей сессии, где часовой пояс America / Los_Angeles.

Теперь давайте изменим часовой пояс сеанса.

mysql> SET time_zone='America/New_York';
Query OK, 0 rows affected (0.01 sec)
mysql> SELECT * FROM time_test;
+---------------------+---------------------+---------------------+
| timezone            | time_datetime       | time_timestamp      |
+---------------------+---------------------+---------------------+
| America/Los_Angeles | 2020-03-12 11:00:00 | 2020-03-12 14:00:00 |
+---------------------+---------------------+---------------------+
1 row in set (0.00 sec)
  • Часовой пояс сеанса: America / New_York
  • Получить данные. Столбец DATETIME по-прежнему показывает то же время 11:00:00, однако столбец TIMESTAMP преобразует время в America / New_York, что составляет 14:00:00.

Теперь давайте вставим другое время в часовой пояс America / New_York:

mysql> INSERT INTO time_test VALUES (@@SESSION.time_zone, '2020-03-12 11:00:00', '2020-03-12 11:00:00');
Query OK, 1 row affected (0.01 sec)
mysql> SELECT * FROM time_test;
+---------------------+---------------------+---------------------+
| timezone            | time_datetime       | time_timestamp      |
+---------------------+---------------------+---------------------+
| America/Los_Angeles | 2020-03-12 11:00:00 | 2020-03-12 14:00:00 |
| America/New_York    | 2020-03-12 11:00:00 | 2020-03-12 11:00:00 |
+---------------------+---------------------+---------------------+
2 rows in set (0.00 sec)

Давайте переключим часовой пояс сеанса на UTC:

mysql> SET time_zone='UTC';
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT * FROM time_test;
+---------------------+---------------------+---------------------+
| timezone            | time_datetime       | time_timestamp      |
+---------------------+---------------------+---------------------+
| America/Los_Angeles | 2020-03-12 11:00:00 | 2020-03-12 18:00:00 |
| America/New_York    | 2020-03-12 11:00:00 | 2020-03-12 15:00:00 |
+---------------------+---------------------+---------------------+
2 rows in set (0.00 sec)

Как и ожидалось, столбец DATETIME показывает последовательные 11:00:00, но столбец TIMESTAMP показывает время, преобразованное из America / Los_Angeles и America / New_York в UTC, соответственно (это упражнение выполняется в режиме летнего времени, поэтому разница во времени между UTC и America / Los_Angeles +07: 00, а America / New_York +04: 00).

CONVERT_TZ

MySQL предоставляет встроенную функцию CONVERT_TZ, которая помогает преобразовывать метку времени из одного часового пояса в другой. Он автоматически заботится о переходе на летнее время. Для использования этой функции в вашем экземпляре MySQL должны быть заполнены таблицы часовых поясов во внутренней базе данных mysql. Инструкцию по загрузке таблиц часовых поясов можно найти по адресу https://dev.mysql.com/doc/refman/8.0/en/mysql-tzinfo-to-sql.html.

CONVERT_TZ работает как с DATETIME, так и с TIMESTAMP. Пример,

mysql> SET time_zone='UTC';
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT timezone, CONVERT_TZ(time_datetime, timezone, @@SESSION.time_zone) as time_datetime, time_timestamp FROM time_test;
+---------------------+---------------------+---------------------+
| timezone            | time_datetime       | time_timestamp      |
+---------------------+---------------------+---------------------+
| America/Los_Angeles | 2020-03-12 18:00:00 | 2020-03-12 18:00:00 |
| America/New_York    | 2020-03-12 15:00:00 | 2020-03-12 15:00:00 |
+---------------------+---------------------+---------------------+
2 rows in set (0.00 sec)

Теперь оба выходных столбца последовательно конвертируются в UTC.

В чем же тогда прецедент применения CONVERT_TZ к значениям TIMESTAMP, если выходные данные TIMESTAMP автоматически преобразуются в часовой пояс сеанса?

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

mysql> SET time_zone='UTC';
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT timezone, CONVERT_TZ(time_datetime, timezone, @@SESSION.time_zone) as time_datetime, CONVERT_TZ(time_timestamp, @@SESSION.time_zone, 'America/New_York') as time_timestamp FROM time_test;
+---------------------+---------------------+---------------------+
| timezone            | time_datetime       | time_timestamp      |
+---------------------+---------------------+---------------------+
| America/Los_Angeles | 2020-03-12 18:00:00 | 2020-03-12 14:00:00 |
| America/New_York    | 2020-03-12 15:00:00 | 2020-03-12 11:00:00 |
+---------------------+---------------------+---------------------+
2 rows in set (0.00 sec)
mysql> SET time_zone='Asia/Shanghai';
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT timezone, CONVERT_TZ(time_datetime, timezone, @@SESSION.time_zone) as time_datetime, CONVERT_TZ(time_timestamp, @@SESSION.time_zone, 'America/New_York') as time_timestamp FROM time_test;
+---------------------+---------------------+---------------------+
| timezone            | time_datetime       | time_timestamp      |
+---------------------+---------------------+---------------------+
| America/Los_Angeles | 2020-03-13 02:00:00 | 2020-03-12 14:00:00 |
| America/New_York    | 2020-03-12 23:00:00 | 2020-03-12 11:00:00 |
+---------------------+---------------------+---------------------+
2 rows in set (0.00 sec)

Обратите внимание, что когда вы CONVERT_TZ a TIMESTAMP, исходный часовой пояс является часовым поясом сеанса.

Пожалуйста, прочтите другие уроки из моей серии статей о часовом поясе в MySQL:



Сравнение TIMESTAMP с часовым поясом в MySQL
Вот воображаемая таблица с индексированным TIMESTAMP: medium.com





Таинственный переход на летнее время в MySQL
DST 2020 только что началось в прошлое воскресенье. Долгое время мне представлялось, что происходит в течение этого часа, ты… medium.com