Робота з часовими поясами: 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 по часу і часовим поясам — дуже корисна штука.