Работа с временными зонами: TIMEZONE

Допустим, у вас есть приложение для бронирования авиабилетов. Рейс вылетает из Нью-Йорка в 10:00 по местному времени и прилетает в Лондон в 22:00 по местному времени. Без учёта временных зон ваш сервер может превратить это в абсолютный хаос, показывая неправильное время прилёта.

Временные зоны — это ваши самые верные друзья (или самые злобные враги, когда всё идёт не по плану). Если ваши пользователи находятся в разных странах или вам нужно работать с расписаниями, которые зависят от местного времени (например, время рейсов или расписание мероприятий), тогда учёт временных зон становится критически важным.

Типы временных данных

Мы уже обсуждали, что есть два типа данных для работы с временными отметками:

• TIMESTAMP: дата и время без учета временной зоны.
• TIMESTAMPTZ: дата и время с учётом временной зоны.

Давайте разберём их ещё раз на примере.

-- Создаём таблицу с двумя колонками: TIMESTAMP и TIMESTAMPTZ
CREATE TABLE flight_schedule (
    flight_id SERIAL PRIMARY KEY,
    departure_time TIMESTAMP,
    departure_time_with_tz TIMESTAMPTZ
);

-- Вставляем данные
INSERT INTO flight_schedule (departure_time, departure_time_with_tz)
VALUES
    ('2023-10-25 10:00:00', '2023-10-25 10:00:00+00');

-- Проверим данные
SELECT * FROM flight_schedule;

Результат будет зависеть от временной зоны вашего сервера. Например:

Ключевое различие:

• Колонка departure_time просто хранит дату и время без привязки к какому-либо часовому поясу.
• Колонка departure_time_with_tz хранит дату и время вместе с информацией о временной зоне (+00 в данном случае).

Преобразование времени в разные временные зоны

Для работы с временными зонами в PostgreSQL используется функция AT TIME ZONE.

Преобразование UTC в локальное время

Предположим, у нас есть временная отметка в формате UTC (всемирное координированное время). Мы хотим отобразить её для пользователя, который находится в часовом поясе America/New_York.

SELECT
    '2023-10-25 14:00:00+00'::TIMESTAMPTZ AT TIME ZONE 'America/New_York' AS local_time;

Результат:

AT TIME ZONE здесь работает как волшебная палочка: оно преобразует время из UTC в указанную временную зону.

Преобразование локального времени в UTC

Теперь представим обратную задачу: у нас есть время в America/New_York, и мы хотим преобразовать его в UTC.

SELECT
    '2023-10-25 10:00:00'::TIMESTAMP AT TIME ZONE 'America/New_York' AS utc_time;

Результат:

Обратите внимание, что результат будет в формате TIMESTAMPTZ, так как он включает информацию о временной зоне (UTC в данном случае).

Работа с типом данных TIMESTAMPTZ

Когда вы работаете с TIMESTAMPTZ, PostgreSQL автоматически учитывает временную зону вашего сервера (или заданную вами).

Вы можете задать временную зону для текущей сессии с помощью команды:

SET TIMEZONE = 'Europe/Istanbul';

После этого все операции с TIMESTAMPTZ будут выполняться с учётом этой временной зоны.

Пример: вставка и выборка данных

-- Установим временную зону
SET TIMEZONE = 'Europe/Istanbul';

-- Вставим данные
INSERT INTO flight_schedule (departure_time_with_tz)
VALUES ('2023-10-25 10:00:00+00');

-- Проверим данные
SELECT departure_time_with_tz FROM flight_schedule;

Результат в временной зоне Europe/Istanbul:

PostgreSQL автоматически преобразует время из UTC с учётом временной зоны, которую вы указали.

Практические примеры

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

SELECT
    flight_id,
    departure_time_with_tz AT TIME ZONE 'America/New_York' AS local_time
FROM flight_schedule;

Сравнение временных данных из разных часовых поясов. Представим, что мы сравниваем два события, произошедших в разных городах. PostgreSQL позволяет это делать, автоматически приводя данные к единой временной зоне.

SELECT
    '2023-10-25 10:00:00+03'::TIMESTAMPTZ > '2023-10-25 07:00:00+00'::TIMESTAMPTZ AS event_one_later;

Результат:

Сравнение вернуло true, так как 10:00+03 эквивалентно 07:00+00.

Советы и частые грабли

Работа с временем — штука коварная. Вот что часто идёт не так:

• Используют TIMESTAMP вместо TIMESTAMPTZ, и потом удивляются, почему время не совпадает — потому что временные зоны просто игнорируются.
• Не знают, в какой временной зоне работает сервер, и в итоге данные вставляются в одном времени, а читаются — в другом.
• Ошибаются в названии часового пояса при использовании AT TIME ZONE — и получают ошибку или не то время.

Чтобы не попасть впросак:

• Почти всегда используйте TIMESTAMPTZ, особенно если данные могут зависеть от временной зоны.
• Храните время в UTC, а уже при выводе переводите в нужный часовой пояс пользователя.
• Если хотите глубже разобраться, вот официальная документация PostgreSQL по времени и временным зонам — очень полезная штука.

2

Задача

SQL SELF, 32 уровень, 2 лекция

Недоступна

Преобразование UTC во временную зону Нью-Йорка

Преобразование UTC во временную зону Нью-Йорка

Открыть