Jadwal berulang dan zona waktu di PostgreSQL: pola

Mengapa zona waktu dan acara berulang sering salah

Sebagian besar bug kalender bukanlah bug matematika. Mereka adalah bug makna. Anda menyimpan satu hal (sebuah instant di waktu), tetapi pengguna mengharapkan hal lain (waktu jam dinding lokal di tempat tertentu). Kesenjangan itu membuat jadwal berulang dan zona waktu tampak benar di tes, lalu rusak ketika pengguna asli muncul.

Daylight Saving Time (DST) adalah pemicu klasik. Perubahan seperti “setiap Minggu jam 09:00” tidak sama dengan “setiap 7 hari dari timestamp awal.” Ketika offset berubah, kedua gagasan itu bergeser satu jam dan kalender Anda diam-diam menjadi salah.

Perjalanan dan campuran zona waktu menambah lapisan lain. Sebuah pemesanan mungkin terkait dengan tempat fisik (kursi salon di Chicago), sementara orang yang melihatnya berada di London. Jika Anda memperlakukan jadwal yang terikat tempat sebagai jadwal yang terikat orang, Anda akan menampilkan waktu lokal yang salah ke setidaknya salah satu pihak.

Mode kegagalan yang umum:

• Anda menghasilkan pengulangan dengan menambahkan interval ke timestamp yang disimpan, lalu DST berubah.
• Anda menyimpan “waktu lokal” tanpa aturan zona, sehingga Anda tidak bisa membangun kembali instant yang dimaksud nanti.
• Anda hanya mengetes tanggal yang tidak pernah melewati batas DST.
• Anda mencampur “zona waktu event”, “zona waktu pengguna”, dan “zona waktu server” dalam satu kueri.

Sebelum memilih skema, tentukan apa arti “benar” untuk produk Anda.

Untuk pemesanan, “benar” biasanya berarti: janji terjadi pada waktu jam dinding yang dimaksud di zona waktu venue, dan semua orang yang melihatnya mendapatkan konversi yang tepat.

Untuk shift, “benar” sering berarti: shift dimulai pada waktu lokal tetap untuk toko, bahkan jika karyawan sedang bepergian.

Keputusan itu (jadwal terikat tempat vs. terikat orang) menentukan segalanya: apa yang Anda simpan, bagaimana Anda menghasilkan pengulangan, dan bagaimana Anda melakukan kueri tampilan kalender tanpa kejutan satu jam.

Pilih model mental yang tepat: instant vs. waktu lokal

Banyak bug berasal dari mencampur dua ide waktu yang berbeda:

• Sebuah instant: momen absolut yang terjadi satu kali.
• Aturan waktu lokal: waktu jam dinding seperti “setiap Senin jam 9:00 AM di Paris.”

Sebuah instant sama di mana saja. “2026-03-10 14:00 UTC” adalah sebuah instant. Panggilan video, keberangkatan penerbangan, dan “kirim notifikasi tepat pada momen ini” biasanya berupa instant.

Waktu lokal adalah yang orang baca di jam di suatu tempat. “09:00 AM di Europe/Paris setiap hari kerja” adalah waktu lokal. Jam buka toko, kelas berulang, dan shift staf biasanya berlabuh ke zona lokasi. Zona waktu adalah bagian dari makna, bukan preferensi tampilan.

Aturan praktis sederhana:

• Simpan start/end sebagai instant ketika event harus terjadi pada satu momen nyata di seluruh dunia.
• Simpan tanggal lokal dan waktu lokal plus ID zona ketika event dimaksudkan mengikuti jam di satu tempat.
• Jika pengguna bepergian, tampilkan waktu dalam zona penonton, tetapi pertahankan jadwal terikat pada zonanya.
• Jangan menebak zona dari offset seperti "+02:00". Offset tidak memuat aturan DST.

Contoh: shift rumah sakit adalah “Sen-Jum 09:00-17:00 America/New_York.” Saat minggu perubahan DST, shift tetap 9 sampai 5 secara lokal, meskipun instant UTC bergeser satu jam.

Tipe PostgreSQL yang penting (dan yang harus dihindari)

Kebanyakan bug kalender dimulai dari tipe kolom yang salah. Kuncinya adalah memisahkan momen nyata dari ekspektasi jam-dinding.

Gunakan timestamptz untuk instant nyata: pemesanan, clock-in, notifikasi, dan apa pun yang Anda bandingkan antar pengguna atau wilayah. PostgreSQL menyimpannya sebagai instant absolut dan mengonversinya untuk tampilan, jadi pengurutan dan pengecekan overlap berperilaku seperti yang diharapkan.

Gunakan timestamp without time zone untuk nilai jam-dinding lokal yang bukan instant sendiri, seperti “setiap Senin jam 09:00” atau “toko buka jam 10:00.” Pasangkan dengan identifier zona waktu, lalu konversi ke instant nyata hanya saat menghasilkan kejadian.

Untuk pola rekuren, tipe dasar membantu:

• date untuk pengecualian berbasis hari (hari libur)
• time untuk waktu mulai harian
• interval untuk durasi (misalnya shift 6 jam)

Simpan zona waktu sebagai nama IANA (misalnya, America/New_York) di kolom text (atau tabel lookup kecil). Offset seperti -0500 tidak cukup karena tidak membawa aturan daylight saving.

Set praktis untuk banyak aplikasi:

• timestamptz untuk start/end instant dari janji yang dipesan
• date untuk hari pengecualian
• time untuk waktu mulai lokal berulang
• interval untuk durasi
• text untuk ID zona waktu IANA

Opsi model data untuk aplikasi booking dan shift

Skema terbaik bergantung pada seberapa sering jadwal berubah dan seberapa jauh orang melihat ke depan. Anda biasanya memilih antara menulis banyak baris di muka atau menghasilkan saat seseorang membuka kalender.

Opsi A: simpan setiap kejadian

Masukkan satu baris per shift atau pemesanan (sudah diperluas). Mudah untuk dikueri dan mudah dipahami. Tradeoff-nya adalah penulisan berat dan banyak pembaruan ketika sebuah aturan berubah.

Ini bekerja baik ketika event sebagian besar bersifat sekali saja, atau ketika Anda hanya membuat kejadian untuk jangka pendek ke depan (misalnya 30 hari ke depan).

Opsi B: simpan aturan dan luaskan saat membaca

Simpan aturan jadwal (mis. “mingguan setiap Senin dan Rabu jam 09:00 di America/New_York”) dan hasilkan kejadian untuk rentang yang diminta saat dibutuhkan.

Fleksibel dan hemat penyimpanan, tapi kueri menjadi lebih rumit. Tampilan bulanan juga bisa melambat kecuali Anda melakukan cache hasil.

Opsi C: aturan plus kejadian cache (hibrida)

Pertahankan aturan sebagai sumber kebenaran, dan juga simpan kejadian yang dihasilkan untuk jendela bergulir (misalnya 60-90 hari). Saat aturan berubah, regenarasi cache.

Ini adalah default kuat untuk aplikasi shift: tampilan bulanan tetap cepat, tetapi Anda tetap punya satu tempat untuk mengedit pola.

Set tabel praktis:

• schedule: owner/resource, time zone, local start time, duration, recurrence rule
• occurrence: instance yang diperluas dengan start_at timestamptz, end_at timestamptz, plus status
• exception: penanda “lewati tanggal ini” atau “tanggal ini berbeda”
• override: edit per-kejadian seperti perubahan start time, swap staf, flag dibatalkan
• (opsional) schedule_cache_state: range terakhir yang dihasilkan supaya Anda tahu apa yang harus diisi berikutnya

Untuk kueri rentang kalender, index untuk “tunjukkan semua dalam jendela ini”:

• Pada occurrence: btree (resource_id, start_at) dan sering btree (resource_id, end_at)
• Jika Anda sering kueri “overlaps range”: sebuah tstzrange(start_at, end_at) yang dihasilkan plus index gist

Merepresentasikan aturan rekuren tanpa membuatnya rapuh

Jadwal rekuren rusak ketika aturan terlalu pintar, terlalu fleksibel, atau disimpan sebagai blob yang tidak bisa dikueri. Format aturan yang baik adalah yang bisa divalidasi oleh aplikasi Anda dan bisa dijelaskan dengan cepat oleh tim.

Dua pendekatan umum:

• Field kustom sederhana untuk pola yang benar-benar Anda dukung (shift mingguan, tanggal tagihan bulanan).
• Aturan mirip iCalendar (gaya RRULE) ketika Anda harus import/export kalender atau mendukung banyak kombinasi.

Titik tengah yang praktis: izinkan set opsi terbatas, simpan di kolom, dan anggap string RRULE hanya untuk pertukaran.

Contoh, aturan shift mingguan bisa diungkapkan dengan field seperti:

• freq (daily/weekly/monthly) dan interval (setiap N)
• byweekday (array 0-6 atau bitmask)
• opsional bymonthday (1-31) untuk aturan bulanan
• starts_at_local (tanggal+waktu lokal yang dipilih pengguna) dan tzid
• opsional until_date atau count (hindari mendukung keduanya kecuali benar-benar perlu)

Untuk batas, lebih baik menyimpan durasi (misalnya 8 jam) daripada menyimpan end timestamp untuk setiap kejadian. Durasi tetap stabil saat jam bergeser. Anda masih bisa menghitung waktu akhir per kejadian sebagai: start kejadian + durasi.

Saat memperluas aturan, jaga agar aman dan terbatas:

• Luaskan hanya dalam window_start dan window_end.
• Tambahkan buffer kecil (mis. 1 hari) untuk event semalaman.
• Hentikan setelah jumlah instance maksimum (mis. 500).
• Saring kandidat terlebih dahulu (berdasarkan tzid, freq, dan tanggal mulai) sebelum menghasilkan.

Langkah demi langkah: bangun jadwal rekuren yang aman terhadap DST

Pola yang dapat diandalkan adalah: perlakukan setiap kejadian sebagai ide kalender lokal terlebih dahulu (tanggal + waktu lokal + zona lokasi), lalu konversi ke instant hanya ketika Anda perlu mengurutkan, memeriksa konflik, atau menampilkannya.

1) Simpan intent lokal, bukan tebakan UTC

Simpan zona waktu lokasi jadwal (nama IANA seperti America/New_York) plus waktu mulai lokal (misalnya 09:00). Waktu lokal ini adalah yang dimaksud bisnis, bahkan saat DST bergeser.

Simpan juga durasi dan batas yang jelas untuk aturan: tanggal mulai, dan entah tanggal akhir atau jumlah pengulangan. Batas mencegah bug “ekspansi tak hingga”.

2) Modelkan pengecualian dan override secara terpisah

Gunakan dua tabel kecil: satu untuk tanggal yang dilewati, satu untuk kejadian yang diubah. Kunci-kan mereka dengan schedule_id + local_date sehingga Anda dapat mencocokkan pengulangan asal dengan bersih.

Bentuk praktisnya terlihat seperti ini:

-- 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) Luaskan hanya di dalam jendela yang diminta

Hasilkan tanggal lokal kandidat untuk rentang yang Anda render (minggu, bulan). Saring berdasarkan hari dalam minggu, lalu terapkan skip dan override.

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) Konversi untuk penonton di tahap akhir

Simpan start_utc sebagai timestamptz untuk pengurutan, pengecekan konflik, dan pemesanan. Hanya saat Anda menampilkan, konversi ke zona penonton. Ini menghindari kejutan DST dan menjaga tampilan kalender konsisten.

Pola kueri untuk menghasilkan tampilan kalender yang benar

Layar kalender biasanya adalah kueri rentang: “tunjukkan semua antara from_ts dan to_ts.” Pola aman adalah:

1. Luaskan hanya kandidat dalam jendela itu.
2. Terapkan pengecualian/override.
3. Output baris akhir dengan start_at dan end_at sebagai timestamptz.

Ekspansi harian atau mingguan dengan generate_series

Untuk aturan mingguan sederhana (mis. “setiap Senin-Jumat jam 09:00 lokal”), hasilkan tanggal lokal dalam zona aturan, lalu ubah setiap tanggal lokal + waktu lokal menjadi sebuah 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);

Ini bekerja baik karena konversi ke timestamptz terjadi per kejadian, sehingga pergeseran DST diterapkan pada hari yang benar.

Aturan lebih kompleks dengan recursive CTE

Ketika aturan bergantung pada “weekday ke-n”, celah, atau interval kustom, sebuah recursive CTE dapat menghasilkan kejadian berikutnya berulang sampai melewati to_ts. Jaga rekursi tertambat pada jendela sehingga tidak berjalan selamanya.

Setelah Anda punya baris kandidat, terapkan override dan pembatalan dengan join tabel pengecualian pada (rule_id, start_at) atau pada kunci lokal seperti (rule_id, local_date). Jika ada catatan batal, hapus baris itu. Jika ada override, ganti start_at/end_at dengan nilai override.

Polapola performa yang paling penting:

• Batasi rentang sedini mungkin: saring aturan dulu, lalu luaskan hanya dalam [from_ts, to_ts).
• Index tabel pengecualian/override pada (rule_id, start_at) atau (rule_id, local_date).
• Hindari meluaskan bertahun-tahun data untuk tampilan bulanan.
• Cache kejadian yang diperluas hanya jika Anda bisa menginvalidasinya dengan bersih saat aturan berubah.

Menangani pengecualian dan override dengan rapi

Jadwal rekuren hanya berguna jika Anda bisa memutuskannya dengan aman. Di aplikasi booking dan shift, “minggu normal” adalah aturan dasar, dan semua hal lain adalah pengecualian: hari libur, pembatalan, janji yang dipindah, atau pertukaran staf. Jika pengecualian ditambahkan belakangan, tampilan kalender akan bergeser dan duplikat muncul.

Pisahkan tiga konsep:

• Jadwal dasar (aturan rekuren dan zona waktunya)
• Skips (tanggal atau instance yang tidak boleh terjadi)
• Overrides (sebuah kejadian yang ada, tetapi dengan detail yang diubah)

Gunakan urutan precedence yang tetap

Pilih satu urutan dan pertahankan konsistensi. Pilihan umum:

1. Hasilkan kandidat dari rekuren dasar.
2. Terapkan overrides (ganti yang dihasilkan).
3. Terapkan skips (sembunyikan).

Pastikan aturan mudah dijelaskan ke pengguna dalam satu kalimat.

Hindari duplikat ketika override menggantikan sebuah instance

Duplikat biasanya terjadi ketika kueri mengembalikan baik kejadian yang dihasilkan maupun baris override. Cegah itu dengan kunci stabil:

• Beri setiap instance yang dihasilkan kunci stabil, seperti (schedule_id, local_date, start_time, tzid).
• Simpan kunci itu di baris override sebagai “original occurrence key.”
• Tambahkan constraint unik sehingga hanya satu override yang ada per kejadian dasar.

Lalu, dalam kueri, kecualikan kejadian yang dihasilkan yang memiliki override yang cocok dan union baris override.

Pertahankan audit tanpa gesekan

Pengecualian adalah tempat terjadi sengketa (“Siapa yang mengubah shift saya?”). Tambahkan field audit dasar pada skips dan overrides: created_by, created_at, updated_by, updated_at, dan alasan opsional.

Kesalahan umum yang menyebabkan bug selisih-satu-jam

Sebagian besar bug satu jam datang dari mencampur dua makna waktu: sebuah instant (titik pada timeline UTC) dan pembacaan jam lokal (mis. 09:00 setiap Senin di New York).

Kesalahan klasik adalah menyimpan aturan jam-dinding lokal sebagai timestamptz. Jika Anda menyimpan “Senin jam 09:00 America/New_York” sebagai satu timestamptz, Anda sudah memilih tanggal spesifik (dan status DST). Nanti, ketika Anda menghasilkan Senin berikutnya, maksud awal (“selalu 09:00 lokal”) hilang.

Penyebab lain yang sering adalah mengandalkan offset UTC tetap seperti -05:00 alih-alih nama zona IANA. Offset tidak memasukkan aturan DST. Simpan ID zona (mis. America/New_York) dan biarkan PostgreSQL menerapkan aturan yang benar untuk setiap tanggal.

Hati-hati tentang kapan Anda mengonversi. Jika Anda mengonversi ke UTC terlalu dini saat menghasilkan rekuren, Anda bisa membekukan offset DST dan menerapkannya ke setiap kejadian. Pola yang lebih aman: hasilkan kejadian dalam istilah lokal (tanggal + waktu lokal + zona), lalu konversi setiap kejadian ke instant.

Kesalahan yang sering muncul berulang:

• Menggunakan timestamptz untuk menyimpan waktu lokal berulang (seharusnya time + tzid + aturan).
• Hanya menyimpan offset, bukan nama zona IANA.
• Mengonversi selama generasi rekuren alih-alih di akhir.
• Meluaskan rekuren “selamanya” tanpa jendela waktu keras.
• Tidak menguji minggu perubahan DST awal dan akhir.

Tes sederhana yang menangkap sebagian besar isu: pilih zona dengan DST, buat shift mingguan jam 09:00, dan render kalender dua bulan yang melewati perubahan DST. Verifikasi setiap instance tampil sebagai 09:00 lokal, meskipun instant UTC di baliknya berbeda.

Daftar periksa cepat sebelum rilis

Sebelum rilis, periksa dasar-dasarnya:

• Setiap jadwal terikat ke sebuah tempat (atau unit bisnis) dengan zona waktu bernama, disimpan di jadwal itu sendiri.
• Anda menyimpan ID zona IANA (seperti America/New_York), bukan offset mentah.
• Ekspansi rekuren menghasilkan kejadian hanya di dalam rentang yang diminta.
• Pengecualian dan override memiliki urutan precedence yang terdokumentasi tunggal.
• Anda menguji minggu perubahan DST dan seorang penonton di zona waktu berbeda dari jadwal.

Lakukan satu dry run realistis: sebuah toko di Europe/Berlin memiliki shift mingguan jam 09:00 waktu lokal. Seorang manajer melihatnya dari America/Los_Angeles. Pastikan shift tetap 09:00 waktu Berlin setiap minggu, bahkan ketika masing-masing wilayah melewati DST pada tanggal yang berbeda.

Contoh: shift staf mingguan dengan hari libur dan perubahan DST

Sebuah klinik kecil menjalankan satu shift rekuren: setiap Senin, 09:00–17:00 di zona waktu klinik (America/New_York). Klinik tutup untuk hari libur pada satu Senin tertentu. Seorang staf sedang bepergian di Eropa selama dua minggu, tetapi jadwal klinik harus tetap terikat pada jam dinding klinik, bukan lokasi karyawan.

Untuk membuat ini berperilaku benar:

• Simpan aturan rekuren yang berlabuh pada tanggal lokal (weekday = Monday, waktu lokal = 09:00–17:00).
• Simpan zona waktu jadwal (America/New_York).
• Simpan tanggal mulai efektif sehingga aturan punya jangkar jelas.
• Simpan pengecualian untuk membatalkan Senin libur itu (dan override untuk perubahan satu kali).

Sekarang render kalender dua minggu yang mencakup perubahan DST di New York. Kueri menghasilkan hari Senin di rentang tanggal lokal itu, melampirkan waktu lokal klinik, lalu mengonversi setiap kejadian menjadi instant absolut (timestamptz). Karena konversi terjadi per kejadian, DST ditangani pada hari yang benar.

Penonton yang berbeda melihat waktu jam lokal yang berbeda untuk instant yang sama:

• Seorang manajer di Los Angeles melihatnya lebih awal di jamnya.
• Seorang staf bepergian di Berlin melihatnya lebih lambat di jamnya.

Klinik tetap mendapatkan apa yang diinginkan: 09:00–17:00 waktu New York, setiap Senin yang tidak dibatalkan.

Langkah berikutnya: implementasi, pengujian, dan jaga agar tetap terawat

Kunci pendekatan terhadap waktu sedini mungkin: apakah Anda akan menyimpan hanya aturan, hanya kejadian, atau hibrida? Untuk banyak produk booking dan shift, hibrida bekerja baik: simpan aturan sebagai sumber kebenaran, simpan cache bergulir jika perlu, dan simpan pengecualian serta override sebagai baris konkret.

Tuliskan “kontrak waktu” Anda di satu tempat: apa yang dihitung sebagai instant, apa yang dihitung sebagai waktu jam-dinding lokal, dan kolom mana yang menyimpan masing-masing. Ini mencegah penyimpangan di mana satu endpoint mengembalikan waktu lokal sementara yang lain mengembalikan UTC.

Jaga agar logika generasi rekuren menjadi satu modul, bukan potongan SQL yang tersebar. Jika Anda pernah mengubah interpretasi “09:00 AM waktu lokal,” Anda ingin satu tempat untuk memperbarui.

Jika Anda membangun alat penjadwalan tanpa menulis semuanya secara manual, AppMaster (appmaster.io) adalah pilihan praktis untuk jenis pekerjaan ini: Anda dapat memodelkan database di Data Designer-nya, membangun logika rekuren dan pengecualian dalam business process visual, dan tetap mendapatkan backend dan kode aplikasi yang nyata.