Harmonogramy cykliczne i strefy czasowe w PostgreSQL — wzorce

Dlaczego harmonogramy i strefy czasowe się psują

Większość błędów w kalendarzach to nie błędy matematyczne, a błędy znaczenia. Przechowujesz jedną rzecz (moment w czasie), a użytkownicy oczekują innej (lokalny czas na zegarze w konkretnym miejscu). Ta luka powoduje, że harmonogramy cykliczne i strefy czasowe mogą wyglądać dobrze w testach, a psuć się, gdy pojawiają się prawdziwi użytkownicy.

Przełączanie na czas letni (DST) to klasyczny wyzwalacz. "Co niedzielę o 09:00" nie jest tym samym, co „co 7 dni od daty początkowej”. Gdy offset zmienia się, te dwa pomysły przesuwają się o godzinę i kalendarz po cichu staje się błędny.

Podróże i mieszane strefy czasowe dodają kolejną warstwę. Rezerwacja może być związana z miejscem (fotel w salonie w Chicago), a osoba ją przeglądająca jest w Londynie. Jeśli potraktujesz harmonogram związany z miejscem jako związany z osobą, co najmniej jedna strona zobaczy zły lokalny czas.

Typowe tryby awarii:

• Generujesz wystąpienia, dodając odstęp do zapisanego znacznika czasu, a potem zmienia się DST.
• Przechowujesz „czasy lokalne” bez zasad strefy, więc nie możesz odtworzyć zamierzonego momentu później.
• Testujesz tylko daty, które nigdy nie przechodzą przez granicę DST.
• Mieszasz „strefę zdarzenia”, „strefę użytkownika” i „strefę serwera” w jednym zapytaniu.

Zanim wybierzesz schemat, zdecyduj, co dla twojego produktu znaczy „poprawne”.

Dla rezerwacji „poprawne” zwykle oznacza: wizyta odbywa się o zamierzonym czasie na zegarze ściennym w strefie czasowej miejsca, a każdy, kto ją ogląda, dostaje poprawne przeliczenie.

Dla zmiany (shiftu) „poprawne” często znaczy: zmiana zaczyna się o stałym lokalnym czasie dla sklepu, nawet jeśli pracownik podróżuje.

Ta jedna decyzja (harmonogram związany z miejscem vs. osobą) determinuje wszystko: co przechowujesz, jak generujesz wystąpienia i jak zapytujesz widok kalendarza bez niespodzianek o godzinę.

Wybierz właściwy model myślowy: instant vs. czas lokalny

Wiele błędów wynika z mieszania dwóch różnych pojęć czasu:

• Instant: absolutny moment, który zdarza się raz.
• Reguła czasu lokalnego: czas na zegarze, np. „co poniedziałek o 9:00 w Paryżu”.

Instant jest taki sam wszędzie. „2026-03-10 14:00 UTC” to instant. Wideokonferencje, odloty i „wyślij powiadomienie dokładnie w tym momencie” to zwykle instants.

Czas lokalny to to, co ludzie widzą na zegarze w danym miejscu. „9:00 w Europe/Paris w dni robocze” to czas lokalny. Godziny otwarcia, zajęcia cykliczne i zmiany personelu zwykle są zakotwiczone w strefie lokalnej. Strefa czasowa jest częścią znaczenia, nie tylko preferencją wyświetlania.

Prosta zasada:

• Przechowuj start/koniec jako instants, gdy zdarzenie musi odbyć się w jednym rzeczywistym momencie na świecie.
• Przechowuj lokalną datę i lokalny czas plus identyfikator strefy, gdy zdarzenie ma podążać za zegarem w jednym miejscu.
• Jeśli użytkownicy podróżują, pokazuj czasy w strefie widza, ale trzymaj harmonogram zakotwiczony w jego strefie.
• Nie zgaduj strefy z offsetów typu "+02:00". Offsety nie zawierają zasad DST.

Przykład: zmiana w szpitalu to „Pon–Pt 09:00–17:00 America/New_York”. W tygodniu zmiany DST zmiana nadal trwa lokalnie 9–17, chociaż instants w UTC przesuwają się o godzinę.

Typy PostgreSQL, które mają znaczenie (i czego unikać)

Większość błędów w kalendarzach zaczyna się od złego typu kolumny. Klucz to rozdzielenie prawdziwego momentu od oczekiwania zegara ściennego.

Używaj timestamptz dla prawdziwych instants: rezerwacji, odbić zegarowych (clock-ins), powiadomień i wszystkiego, co porównujesz między użytkownikami lub regionami. PostgreSQL przechowuje to jako absolutny moment i konwertuje do wyświetlania, więc sortowanie i sprawdzanie nachodzenia działa zgodnie z oczekiwaniami.

Używaj timestamp without time zone dla wartości lokalnych zegara, które same w sobie nie są instants, jak „co poniedziałek o 09:00” lub „sklep otwiera się o 10:00”. Sparuj to z identyfikatorem strefy, a do prawdziwego momentu konwertuj dopiero przy generowaniu wystąpień.

Dla wzorców powtarzania przydatne typy:

• date dla wyjątków ograniczonych do dnia (święta)
• time dla lokalnego czasu rozpoczęcia dnia
• interval dla długości trwania (np. 6-godzinna zmiana)

Przechowuj strefę czasową jako nazwę IANA (np. America/New_York) w kolumnie text (lub w małej tabeli słownikowej). Offsety typu -0500 nie wystarczą, bo nie zawierają zasad DST.

Praktyczny zestaw dla wielu aplikacji:

• timestamptz dla startu/końca instants zarezerwowanych terminów
• date dla dni wyjątków
• time dla lokalnego czasu startu
• interval dla długości trwania
• text dla identyfikatora strefy IANA

Opcje modelu danych dla aplikacji rezerwacji i zmian

Najlepszy schemat zależy od tego, jak często harmonogramy się zmieniają i jak daleko do przodu przeglądają użytkownicy. Zwykle wybierasz między zapisaniem wielu wierszy z góry a generowaniem ich przy odczycie.

Opcja A: przechowuj każde wystąpienie

Wstawiaj jeden wiersz na zmianę lub rezerwację (już rozszerzoną). To łatwe do zapytania i łatwe do przemyślenia. Kosztem są cięższe zapisy i wiele aktualizacji, gdy reguła się zmieni.

Dobrze działa, gdy zdarzenia są głównie pojedyncze, albo gdy tworzysz wystąpienia tylko na krótki okres do przodu (np. następne 30 dni).

Opcja B: przechowuj regułę i rozwijaj przy odczycie

Przechowuj regułę harmonogramu (np. „co tydzień w Pon i Śr o 09:00 w America/New_York”) i generuj wystąpienia dla żądanego zakresu na żądanie.

To elastyczne i oszczędne pod względem przestrzeni, ale zapytania stają się bardziej złożone. Widoki miesięczne mogą też być wolniejsze, chyba że cache’ujesz wyniki.

Opcja C: reguła plus cache wystąpień (hybryda)

Trzymaj regułę jako źródło prawdy, a także zapisuj wygenerowane wystąpienia dla okna przesuwnego (np. 60–90 dni). Gdy reguła się zmieni, zregeneruj cache.

To mocny domyślny wybór dla aplikacji zmian: widoki miesięczne są szybkie, a edycja wzorca odbywa się w jednym miejscu.

Praktyczny zestaw tabel:

• schedule: właściciel/zasób, strefa czasowa, lokalny czas startu, długość, reguła powtarzania
• occurrence: rozszerzone wystąpienia z start_at timestamptz, end_at timestamptz oraz status
• exception: znaczniki „pomiń tę datę” lub „ta data jest inna”
• override: edycje per-wystąpienie jak zmieniony start, zmieniony pracownik, flaga anulowania
• (opcjonalnie) schedule_cache_state: ostatnio wygenerowany zakres, żeby wiedzieć, co uzupełnić dalej

Dla zapytań zakresowych kalendarza indeksuj pod „pokaż mi wszystko w tym oknie”:

• Na occurrence: btree (resource_id, start_at) i często btree (resource_id, end_at)
• Jeśli często pytasz o „nachodzenie zakresu”: wygenerowany tstzrange(start_at, end_at) plus indeks gist

Reprezentowanie reguł powtarzania bez łamania ich na fragmencie

Harmonogramy cykliczne psują się, gdy reguła jest zbyt sprytna, zbyt elastyczna lub zapisana jako nieprzydatny blob. Dobry format reguły to taki, który aplikacja może walidować, a zespół wytłumaczyć krótko.

Dwa powszechne podejścia:

• Proste pola własne dla wzorców, które faktycznie obsługujesz (tygodniowe zmiany, miesięczne daty rozliczeń).
• Reguły w stylu iCalendar (RRULE) gdy musisz importować/eksportować kalendarze lub wspierać wiele kombinacji.

Praktyczny kompromis: pozwól na ograniczony zestaw opcji, przechowuj je w kolumnach i traktuj dowolny ciąg RRULE jako format wymiany.

Na przykład regułę tygodniową można wyrazić polami:

• freq (daily/weekly/monthly) i interval (co N)
• byweekday (tablica 0-6 lub maska bitowa)
• opcjonalne bymonthday (1–31) dla reguł miesięcznych
• starts_at_local (lokalna data+godzina, którą wybrał użytkownik) i tzid
• opcjonalne until_date lub count (unikaj obsługi obu naraz, chyba że naprawdę potrzebujesz)

Dla granic preferuj przechowywanie długości trwania (np. 8 godzin) zamiast zapisywania czasu zakończenia dla każdego wystąpienia. Długość trwania pozostaje stabilna przy przesunięciach zegara. Nadal możesz policzyć czas zakończenia dla wystąpienia jako: start + duration.

Podczas rozszerzania reguły trzymaj ją bezpieczną i ograniczoną:

• Rozwijaj tylko wewnątrz window_start i window_end.
• Dodaj mały bufor (np. 1 dzień) dla zdarzeń wielodniowych.
• Przerwij po maksymalnej liczbie instancji (np. 500).
• Najpierw filtruj kandydatów (po tzid, freq i dacie startu) zanim wygenerujesz.

Krok po kroku: zbuduj harmonogram cykliczny bezpieczny wobec DST

Niezawodny wzorzec: traktuj każde wystąpienie najpierw jako ideę kalendarza lokalnego (data + lokalny czas + strefa miejsca), a potem konwertuj na instant tylko, gdy musisz posortować, sprawdzić konflikty lub wyświetlić.

1) Przechowuj lokalny zamiar, nie UTC-owe zgadywanie

Zapisz strefę miejsca (nazwa IANA, np. America/New_York) oraz lokalny czas startu (np. 09:00). Ten lokalny czas to to, co biznes faktycznie ma na myśli, nawet gdy DST się zmienia.

Zapisz też długość trwania i jasne granice reguły: datę rozpoczęcia i albo datę zakończenia, albo liczbę powtórzeń. Granice zapobiegają „nieskończonym rozszerzeniom”.

2) Modeluj wyjątki i nadpisania osobno

Użyj dwóch małych tabel: jednej dla pominiętych dat, drugiej dla zmienionych wystąpień. Kluczuj je przez schedule_id + local_date, abyś mógł dopasować oryginalne wystąpienie czysto.

Praktyczny kształt wygląda tak:

-- core schedule
-- tz is the location time zone
-- start_time is local wall-clock time
schedule(id, tz text, start_date date, end_date date, start_time time, duration_mins int, by_dow int[])

schedule_skip(schedule_id, local_date date)

schedule_override(schedule_id, local_date date, new_start_time time, new_duration_mins int)

3) Rozwijaj tylko w żądanym oknie

Generuj kandydackie lokalne daty dla zakresu, który renderujesz (tydzień, miesiąc). Filtruj po dniu tygodnia, potem zastosuj pominięcia i nadpisania.

WITH days AS (
  SELECT d::date AS local_date
  FROM generate_series($1::date, $2::date, interval '1 day') d
), base AS (
  SELECT s.id, s.tz, days.local_date,
         make_timestamp(extract(year from days.local_date)::int,
                        extract(month from days.local_date)::int,
                        extract(day from days.local_date)::int,
                        extract(hour from s.start_time)::int,
                        extract(minute from s.start_time)::int, 0) AS local_start
  FROM schedule s
  JOIN days ON days.local_date BETWEEN s.start_date AND s.end_date
  WHERE extract(dow from days.local_date)::int = ANY (s.by_dow)
)
SELECT b.id,
       (b.local_start AT TIME ZONE b.tz) AS start_utc
FROM base b
LEFT JOIN schedule_skip sk
  ON sk.schedule_id = b.id AND sk.local_date = b.local_date
WHERE sk.schedule_id IS NULL;

4) Konwertuj dla widza na samym końcu

Trzymaj start_utc jako timestamptz do sortowania, sprawdzania konfliktów i rezerwacji. Dopiero przy wyświetlaniu konwertuj do strefy widza. To unika niespodzianek związanych z DST i utrzymuje spójność widoków kalendarza.

Wzorce zapytań do generowania poprawnego widoku kalendarza

Ekran kalendarza to zwykle zapytanie zakresowe: „pokaż mi wszystko między from_ts i to_ts.” Bezpieczny wzorzec to:

1. Rozwiń tylko kandydatów w tym oknie.
2. Zastosuj wyjątki/nadpisania.
3. Wyprodukuj końcowe wiersze z start_at i end_at jako timestamptz.

Codzienne lub tygodniowe rozwinięcie z generate_series

Dla prostych reguł tygodniowych (np. „co Pon–Pt o 09:00 lokalnie”) wygeneruj lokalne daty w strefie harmonogramu, a potem przekształć każdą lokalną datę + lokalny czas w instant.

-- Inputs: :from_ts, :to_ts are timestamptz
-- rule.tz is an IANA zone like 'America/New_York'
WITH bounds AS (
  SELECT
    (:from_ts AT TIME ZONE rule.tz)::date AS from_local_date,
    (:to_ts   AT TIME ZONE rule.tz)::date AS to_local_date
  FROM rule
  WHERE rule.id = :rule_id
), days AS (
  SELECT d::date AS local_date
  FROM bounds, generate_series(from_local_date, to_local_date, interval '1 day') AS g(d)
)
SELECT
  (local_date + rule.start_local_time) AT TIME ZONE rule.tz AS start_at,
  (local_date + rule.end_local_time)   AT TIME ZONE rule.tz AS end_at
FROM rule
JOIN days ON true
WHERE EXTRACT(ISODOW FROM local_date) = ANY(rule.by_isodow);

To działa dobrze, ponieważ konwersja do timestamptz dzieje się dla każdego wystąpienia, więc przesunięcia DST stosowane są dla właściwego dnia.

Bardziej złożone reguły z rekurencyjnym CTE

Gdy reguły zależą od „n-tego dnia tygodnia”, luk lub niestandardowych odstępów, rekurencyjny CTE może generować kolejne wystąpienia aż przekroczy to_ts. Trzymaj rekursję zakotwiczoną w oknie, żeby nie mogła działać w nieskończoność.

Po uzyskaniu kandydackich wierszy zastosuj nadpisania i anulowania przez dołączenie tabel wyjątków na (rule_id, start_at) lub na klucz lokalny jak (rule_id, local_date). Jeśli istnieje rekord anulowania, usuń wiersz. Jeśli istnieje nadpisanie, zastąp start_at/end_at wartościami z nadpisania.

Wzorce wydajności, które mają największe znaczenie:

• Ogranicz zakres wcześnie: najpierw filtruj reguły, potem rozwijaj tylko wewnątrz [from_ts, to_ts).
• Indeksuj tabele wyjątków/nadpisów po (rule_id, start_at) lub (rule_id, local_date).
• Unikaj rozwijania lat danych dla widoku miesięcznego.
• Cache'uj rozszerzone wystąpienia tylko wtedy, gdy możesz je czysto unieważnić po zmianach reguł.

Czysta obsługa wyjątków i nadpisań

Harmonogramy cykliczne są użyteczne tylko wtedy, gdy możesz je bezpiecznie przerwać. W aplikacjach rezerwacji i zmian „normalny” tydzień to reguła bazowa, a wszystko inne to wyjątek: święta, anulowania, przesunięte wizyty czy zamiany personelu. Jeśli wyjątki są doklejane później, widoki kalendarza się rozjeżdżają i pojawiają się duplikaty.

Trzymaj trzy pojęcia osobno:

• Reguła bazowa (reguła cykliczna i jej strefa czasowa)
• Skips (daty lub instancje, które nie mają się odbyć)
• Overrides (wystąpienia, które istnieją, ale z zmienionymi szczegółami)

Użyj stałego porządku priorytetów

Wybierz jeden porządek i trzymaj się go. Częsty wybór:

1. Wygeneruj kandydatów z podstawowej reguły.
2. Zastosuj nadpisania (zastąp wygenerowane wystąpienie).
3. Zastosuj pominięcia (ukryj je).

Upewnij się, że reguła jest łatwa do wytłumaczenia użytkownikowi w jednym zdaniu.

Unikaj duplikatów, gdy nadpisanie zastępuje instancję

Duplikaty zwykle pojawiają się, gdy zapytanie zwraca zarówno wygenerowane wystąpienie, jak i wiersz nadpisania. Zapobiegnij temu dzięki stabilnemu kluczowi:

• Nadaj każdemu wygenerowanemu wystąpieniu stabilny klucz, np. (schedule_id, local_date, start_time, tzid).
• Przechowaj ten klucz w wierszu nadpisania jako „oryginalny klucz wystąpienia”.
• Dodaj unikalne ograniczenie, żeby tylko jedno nadpisanie mogło istnieć na podstawowe wystąpienie.

Następnie w zapytaniach wyklucz wygenerowane wystąpienia, które mają pasujące nadpisanie i złącz wiersze nadpisania.

Zachowaj audytowalność bez tarcia

To w wyjątkach pojawiają się spory („Kto zmienił moją zmianę?”). Dodaj podstawowe pola audytu na skips i overrides: created_by, created_at, updated_by, updated_at oraz opcjonalne pole powodu.

Typowe błędy powodujące przesunięcie o jedną godzinę

Większość jednugodzinnych błędów pochodzi z mieszania dwóch znaczeń czasu: instant (punkt na osi UTC) i lokalny odczyt zegara (np. 09:00 co poniedziałek w Nowym Jorku).

Klasyczny błąd to przechowywanie lokalnej reguły jako timestamptz. Jeśli zapiszesz „poniedziałki o 09:00 America/New_York” jako pojedyncze timestamptz, już wybrałeś konkretną datę (i stan DST). Później, przy generowaniu przyszłych poniedziałków, pierwotny sens („zawsze 09:00 lokalnie”) jest utracony.

Inna częsta przyczyna to poleganie na stałych offsetach UTC jak -05:00 zamiast nazwy strefy IANA. Offsety nie zawierają zasad DST. Przechowuj identyfikator strefy (np. America/New_York) i pozwól PostgreSQL zastosować właściwe zasady dla każdej daty.

Uważaj, kiedy konwertujesz. Jeśli skonwertujesz na UTC zbyt wcześnie podczas generowania wystąpień, możesz „zamrozić” offset DST i zastosować go do każdego wystąpienia. Bezpieczniejszy wzorzec: generuj wystąpienia w kategoriach lokalnych (data + lokalny czas + strefa), a potem konwertuj każde wystąpienie na instant.

Błędy, które często się powtarzają:

• Używanie timestamptz do przechowywania cyklicznego lokalnego czasu dnia (potrzebowałeś time + tzid + reguła).
• Przechowywanie tylko offsetu, a nie nazwy IANA.
• Konwertowanie podczas generowania, zamiast na końcu.
• Rozwijanie „na wieczność” bez twardego okna czasowego.
• Nie testowanie tygodni zmiany DST i tygodni zakończenia DST.

Prosty test wykrywający większość problemów: wybierz strefę z DST, stwórz tygodniową zmianę o 09:00, wyrenderuj dwumiesięczny kalendarz obejmujący zmianę DST. Sprawdź, czy każda instancja pokazuje 09:00 lokalnie, nawet jeśli underlying UTC instants się różnią.

Szybka lista kontrolna przed wdrożeniem

Przed wydaniem sprawdź podstawy:

• Każdy harmonogram jest powiązany z miejscem (lub jednostką biznesową) z nazwaną strefą czasową, zapisaną bezpośrednio na harmonogramie.
• Przechowujesz identyfikatory stref IANA (np. America/New_York), nie surowe offsety.
• Rozszerzanie reguł generuje wystąpienia tylko wewnątrz żądanego zakresu.
• Wyjątki i nadpisania mają jedną, udokumentowaną kolejność priorytetów.
• Testujesz tygodnie zmiany DST i widza w innej strefie niż harmonogram.

Zrób realistyczny suchy przebieg: sklep w Europe/Berlin ma tygodniową zmianę o 09:00 lokalnie. Kierownik przegląda ją z America/Los_Angeles. Potwierdź, że zmiana pozostaje 09:00 czasu berlinskiego co tydzień, nawet gdy obie strefy przechodzą przez DST w różnych datach.

Przykład: tygodniowe zmiany personelu z dniem wolnym i zmianą DST

Mała klinika ma jedną cykliczną zmianę: każdy poniedziałek 09:00–17:00 w lokalnej strefie kliniki (America/New_York). Klinika zamyka się na święto w jednym konkretnym poniedziałku. Pracownik podróżuje po Europie przez dwa tygodnie, ale harmonogram kliniki musi pozostać związany z lokalnym czasem kliniki, a nie aktualną lokalizacją pracownika.

Aby to zachowało poprawne działanie:

• Przechowuj regułę powtarzania zakotwiczoną do lokalnych dat (dzień tygodnia = poniedziałek, lokalne czasy = 09:00–17:00).
• Przechowuj strefę harmonogramu (America/New_York).
• Przechowuj datę początkową, żeby reguła miała jasny punkt odniesienia.
• Przechowuj wyjątek, który anuluje świąteczny poniedziałek (i nadpisania dla jednorazowych zmian).

Teraz wyrenderuj dwutygodniowy zakres kalendarza zawierający zmianę DST w Nowym Jorku. Zapytanie generuje poniedziałki w tym lokalnym zakresie dat, przypisuje lokalne czasy kliniki, a następnie konwertuje każde wystąpienie na absolutny instant (timestamptz). Ponieważ konwersja dzieje się per wystąpienie, DST jest obsłużony dla właściwego dnia.

Różni widzowie zobaczą różne czasy na zegarze dla tego samego instant:

• Kierownik w Los Angeles zobaczy to wcześniej na zegarze.
• Podróżujący pracownik w Berlinie zobaczy to później na zegarze.

Klinika nadal dostaje to, czego chciała: 09:00–17:00 czasu Nowego Jorku, w każdy nieodwołany poniedziałek.

Następne kroki: implementuj, testuj i utrzymuj czytelność

Zabezpiecz podejście do czasu wcześnie: czy będziesz przechowywać tylko reguły, tylko wystąpienia, czy hybrydę? Dla wielu produktów rezerwacyjnych hybryda działa dobrze: trzymaj regułę jako źródło prawdy, przechowuj przesuwne cache wystąpień jeśli trzeba, i zapisuj wyjątki oraz nadpisania jako konkretne wiersze.

Spisz swój „kontrakt czasowy” w jednym miejscu: co liczy się jako instant, co jako lokalny czas zegara i które kolumny przechowują co. To zapobiega dryfowi, gdzie jedno API zwraca czas lokalny, a inne UTC.

Trzymaj generowanie rekurencji w jednym module, a nie rozproszone w SQL-owych fragmentach. Jeśli kiedykolwiek zmienisz interpretację „09:00 lokalnie”, chcesz mieć jedno miejsce do aktualizacji.

Jeśli budujesz narzędzie do planowania bez ręcznego kodowania wszystkiego, AppMaster (appmaster.io) to praktyczne dopasowanie do tego rodzaju pracy: możesz modelować bazę danych w Data Designerze, budować logikę reguł i wyjątków w procesach biznesowych i wciąż otrzymać realny wygenerowany backend oraz kod aplikacji.