Praca ze strefami czasowymi: TIMEZONE

Załóżmy, że masz apkę do rezerwacji biletów lotniczych. Lot startuje z Nowego Jorku o 10:00 lokalnego czasu i ląduje w Londynie o 22:00 lokalnego czasu. Bez ogarnięcia stref czasowych twój serwer może zrobić z tego totalny chaos, pokazując złe godziny przylotu.

Strefy czasowe — to twoi najlepsi ziomale (albo najwięksi wrogowie, jak coś pójdzie nie tak). Jeśli twoi userzy są w różnych krajach albo musisz pracować z rozkładami zależnymi od lokalnego czasu (np. godziny lotów czy eventów), wtedy ogarnięcie stref czasowych jest mega ważne.

Typy danych czasowych

Już gadaliśmy, że są dwa typy danych do pracy ze znacznikami czasu:

• TIMESTAMP: data i czas bez strefy czasowej.
• TIMESTAMPTZ: data i czas ze strefą czasową.

Ogarnijmy to jeszcze raz na przykładzie.

-- Tworzymy tabelę z dwoma kolumnami: TIMESTAMP i TIMESTAMPTZ
CREATE TABLE flight_schedule (
    flight_id SERIAL PRIMARY KEY,
    departure_time TIMESTAMP,
    departure_time_with_tz TIMESTAMPTZ
);

-- Wrzucamy dane
INSERT INTO flight_schedule (departure_time, departure_time_with_tz)
VALUES
    ('2023-10-25 10:00:00', '2023-10-25 10:00:00+00');

-- Sprawdzamy dane
SELECT * FROM flight_schedule;

Wynik zależy od strefy czasowej twojego serwera. Na przykład:

Kluczowa różnica:

• Kolumna departure_time po prostu trzyma datę i czas bez żadnej strefy czasowej.
• Kolumna departure_time_with_tz trzyma datę i czas razem z info o strefie czasowej (+00 w tym przypadku).

Konwersja czasu między strefami czasowymi

Do pracy ze strefami czasowymi w PostgreSQL używasz funkcji AT TIME ZONE.

Konwersja UTC na lokalny czas

Załóżmy, że mamy znacznik czasu w UTC (uniwersalny czas koordynowany). Chcemy go pokazać userowi, który siedzi w strefie America/New_York.

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

Wynik:

AT TIME ZONE działa tu jak magiczna różdżka: zamienia czas z UTC na podaną strefę czasową.

Konwersja lokalnego czasu na UTC

Teraz odwrotnie: mamy czas w America/New_York i chcemy go przerzucić na UTC.

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

Wynik:

Zwróć uwagę, że wynik będzie w formacie TIMESTAMPTZ, bo zawiera info o strefie czasowej (tu UTC).

Praca z typem danych TIMESTAMPTZ

Jak pracujesz z TIMESTAMPTZ, PostgreSQL automatycznie bierze pod uwagę strefę czasową twojego serwera (albo tę, którą ustawisz).

Możesz ustawić strefę czasową dla bieżącej sesji komendą:

SET TIMEZONE = 'Europe/Istanbul';

Po tym wszystkie operacje na TIMESTAMPTZ będą się działy w tej strefie czasowej.

Przykład: insert i select danych

-- Ustawiamy strefę czasową
SET TIMEZONE = 'Europe/Istanbul';

-- Wrzucamy dane
INSERT INTO flight_schedule (departure_time_with_tz)
VALUES ('2023-10-25 10:00:00+00');

-- Sprawdzamy dane
SELECT departure_time_with_tz FROM flight_schedule;

Wynik w strefie Europe/Istanbul:

PostgreSQL automatycznie konwertuje czas z UTC na strefę, którą podałeś.

Praktyczne przykłady

Ogarnięcie stref czasowych dla rozkładów. Załóżmy, że mamy tabelę z rozkładem lotów, gdzie każdy wpis trzyma czas odlotu w UTC. Chcemy pokazać czas odlotu dla każdego lotu w lokalnej strefie.

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

Porównanie danych czasowych z różnych stref. Wyobraź sobie, że porównujesz dwa eventy z różnych miast. PostgreSQL ogarnia to sam, sprowadzając dane do jednej strefy czasowej.

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

Wynik:

Porównanie zwróciło true, bo 10:00+03 to to samo co 07:00+00.

Tipy i typowe wtopy

Praca z czasem — to podchwytliwa sprawa. Oto co często idzie nie tak:

• Używają TIMESTAMP zamiast TIMESTAMPTZ, a potem zdziwko, czemu czas się nie zgadza — bo strefy czasowe są totalnie ignorowane.
• Nie wiedzą, w jakiej strefie działa serwer, więc dane są wrzucane w jednym czasie, a czytane w innym.
• Pomyłka w nazwie strefy przy AT TIME ZONE — i jest error albo zły czas.

Żeby nie wpaść na minę:

• Prawie zawsze używaj TIMESTAMPTZ, szczególnie jeśli dane mogą zależeć od strefy czasowej.
• Trzymaj czas w UTC, a dopiero przy wyświetlaniu konwertuj na strefę usera.
• Jak chcesz wejść głębiej, tu jest oficjalna dokumentacja PostgreSQL o czasie i strefach czasowych — mega przydatna rzecz.