Lịch lặp và múi giờ trong PostgreSQL: mẫu

Tại sao múi giờ và sự kiện lặp lại hay sai\n\nHầu hết lỗi lịch không phải do toán học. Là do ý nghĩa. Bạn lưu một thứ (một thời điểm tuyệt đối), nhưng người dùng mong đợi thứ khác (một giờ đồng hồ địa phương ở một nơi cụ thể). Khoảng cách này là lý do tại sao lịch lặp và múi giờ có thể chạy tốt trong test, rồi vỡ khi người dùng thực sự xuất hiện.\n\nThay đổi giờ mùa hè (DST) là tác nhân kinh điển. Một quy tắc “mỗi Chủ Nhật lúc 09:00” không giống “mỗi 7 ngày kể từ một timestamp bắt đầu.” Khi offset thay đổi, hai ý tưởng đó lệch nhau một giờ và lịch của bạn âm thầm sai.\n\nViệc đi lại và các múi giờ hỗn hợp làm vấn đề phức tạp hơn. Một đặt chỗ có thể gắn với một địa điểm vật lý (một ghế salon ở Chicago), trong khi người xem đang ở London. Nếu bạn xử lý lịch theo người thay vì theo địa điểm, ít nhất một bên sẽ thấy giờ địa phương sai.\n\nCác chế độ thất bại phổ biến:\n\n- Bạn tạo các lần lặp bằng cách cộng interval cho timestamp lưu, rồi DST thay đổi.\n- Bạn lưu “giờ địa phương” mà không có quy tắc vùng, nên không thể tái tạo instant ban đầu sau này.\n- Bạn chỉ test những ngày không vượt qua biên giới DST.\n- Bạn trộn “event time zone”, “user time zone” và “server time zone” trong cùng một truy vấn.\n\nTrước khi chọn schema, hãy quyết định “đúng” nghĩa là gì với sản phẩm của bạn.\n\nVới một booking, “đúng” thường là: cuộc hẹn diễn ra vào giờ đồng hồ mong muốn ở múi giờ của địa điểm, và mọi người nhìn thấy nó được chuyển đúng.\n\nVới một ca làm việc, “đúng” thường là: ca bắt đầu vào giờ địa phương cố định cho cửa hàng, ngay cả khi nhân viên đang đi công tác.\n\nQuyết định này (lịch gắn với địa điểm hay với cá nhân) quyết định mọi thứ khác: bạn lưu gì, cách tạo các lần lặp, và cách truy vấn chế độ xem lịch mà không bị bất ngờ lệch giờ.\n\n## Chọn mô hình tư duy đúng: instant vs. giờ địa phương\n\nNhiều lỗi đến từ việc trộn hai ý tưởng khác nhau về thời gian:\n\n- Một instant: một khoảnh khắc tuyệt đối chỉ xảy ra một lần.\n- Một quy tắc giờ địa phương: giờ trên đồng hồ như “mỗi Thứ Hai lúc 9:00 sáng ở Paris.”\n\nMột instant giống nhau ở mọi nơi. “2026-03-10 14:00 UTC” là một instant. Các cuộc gọi video, giờ khởi hành chuyến bay, và “gửi thông báo vào đúng thời điểm này” thường là instant.\n\nGiờ địa phương là thứ người ta đọc trên đồng hồ ở một nơi. “9:00 sáng ở Europe/Paris mỗi ngày trong tuần” là giờ địa phương. Giờ mở cửa, lớp học định kỳ, và ca làm việc thường được neo theo múi giờ địa điểm. Múi giờ là một phần ý nghĩa, không phải tùy chọn hiển thị.\n\nQuy tắc đơn giản:\n\n- Lưu start/end dưới dạng instant (timestamptz) khi sự kiện phải xảy ra một thời điểm thực sự trên toàn cầu.\n- Lưu ngày địa phương và giờ địa phương cùng với ID vùng khi sự kiện theo đồng hồ ở một nơi.\n- Nếu người dùng du lịch, hiển thị giờ theo múi của người xem, nhưng giữ lịch neo theo múi của địa điểm.\n- Đừng đoán múi từ offset như "+02:00". Offset không chứa quy tắc DST.\n\nVí dụ: ca bệnh viện là “Thứ Hai–Thứ Sáu 09:00–17:00 America/New_York.” Tuần thay đổi DST, ca vẫn là 9–5 theo giờ địa phương, dù các instant UTC dịch chuyển một giờ.\n\n## Các kiểu dữ liệu PostgreSQL quan trọng (và nên tránh gì)\n\nHầu hết lỗi lịch bắt đầu từ kiểu cột sai. Chìa khóa là tách biệt một khoảnh khắc thực tế và một mong đợi theo đồng hồ.\n\nDùng timestamptz cho các instant thực: booking, điểm danh, thông báo, và bất kỳ thứ gì bạn so sánh xuyên người dùng hoặc vùng. PostgreSQL lưu nó như một instant tuyệt đối và chuyển đổi để hiển thị, nên việc sắp xếp và kiểm tra chồng lấp sẽ đúng.\n\nDùng timestamp without time zone cho các giá trị giờ địa phương không phải instant tự chúng, như “mỗi Thứ Hai lúc 09:00” hoặc “cửa hàng mở lúc 10:00.” Ghép nó với một ID múi giờ, rồi chuyển thành instant chỉ khi sinh các lần xuất hiện.\n\nCho các mẫu lặp, các kiểu cơ bản hữu ích:\n\n- date cho ngoại lệ chỉ ngày (ngày lễ)\n- time cho giờ bắt đầu hàng ngày\n- interval cho độ dài (như ca 6 giờ)\n\nLưu múi giờ bằng tên IANA (ví dụ America/New_York) trong cột text (hoặc bảng tra cứu nhỏ). Offset như -0500 không đủ vì chúng không mang quy tắc DST.\n\nMột tập thiết thực cho nhiều app:\n\n- timestamptz cho start/end instant của các cuộc hẹn đã đặt\n- date cho ngày ngoại lệ\n- time cho giờ bắt đầu lặp hàng ngày\n- interval cho độ dài\n- text cho ID múi giờ IANA\n\n## Các lựa chọn mô hình dữ liệu cho app booking và ca làm việc\n\nSchema tốt nhất tùy vào tần suất thay đổi lịch và mức độ mọi người xem trước. Bạn thường chọn giữa việc ghi nhiều hàng lên trước hoặc sinh chúng khi ai đó mở lịch.\n\n### Tùy chọn A: lưu mọi lần xuất hiện\n\nChèn một hàng cho mỗi ca hoặc booking (đã được mở rộng). Dễ truy vấn và dễ suy nghĩ. Đổi lại là ghi nhiều và nhiều cập nhật khi quy tắc thay đổi.\n\nCách này phù hợp khi sự kiện chủ yếu là một lần, hoặc bạn chỉ tạo các lần xuất hiện trong khoảng thời gian ngắn trước mắt (ví dụ, 30 ngày tới).\n\n### Tùy chọn B: lưu quy tắc và mở rộng khi đọc\n\nLưu một quy tắc lịch (như “hàng tuần vào Thứ Hai và Thứ Tư lúc 09:00 ở America/New_York”) và sinh các lần xuất hiện cho phạm vi yêu cầu khi cần.\n\nCách này linh hoạt và tiết kiệm lưu trữ, nhưng truy vấn phức tạp hơn. Chế độ xem tháng cũng có thể chậm hơn trừ khi bạn cache kết quả.\n\n### Tùy chọn C: quy tắc + cache các lần xuất hiện (hybrid)\n\nGiữ quy tắc làm nguồn chân lý, và cũng lưu các lần xuất hiện sinh ra cho một cửa sổ lăn (ví dụ, 60–90 ngày). Khi quy tắc thay đổi, bạn tái sinh cache.\n\nĐây là mặc định mạnh cho app ca: chế độ xem tháng nhanh, nhưng vẫn có một nơi để sửa mẫu.\n\nMột set bảng thực tế:\n\n- schedule: owner/resource, time zone, local start time, duration, recurrence rule\n- occurrence: các instance đã mở rộng với start_at timestamptz, end_at timestamptz, kèm trạng thái\n- exception: đánh dấu “bỏ qua ngày này” hoặc “ngày này khác”\n- override: chỉnh sửa từng lần như đổi giờ bắt đầu, đổi nhân viên, cờ hủy\n- (tùy chọn) schedule_cache_state: phạm vi sinh lần cuối để biết cần thêm gì\n\nCho truy vấn phạm vi lịch, tạo index để “hiển thị mọi thứ trong cửa sổ này”:\n\n- Trên occurrence: btree (resource_id, start_at) và thường btree (resource_id, end_at)\n- Nếu bạn truy vấn “overlaps range” thường xuyên: một cột tstzrange(start_at, end_at) sinh và gist index\n\n## Biểu diễn quy tắc lặp mà không làm nó mỏng manh\n\nLịch lặp vỡ khi quy tắc quá thông minh, quá linh hoạt, hoặc lưu như một blob không thể truy vấn. Định dạng quy tắc tốt là thứ app của bạn có thể validate và đội ngũ giải thích nhanh.\n\nHai cách phổ biến:\n\n- Trường tùy chỉnh đơn giản cho các mẫu bạn thực sự hỗ trợ (ca hàng tuần, ngày thanh toán hàng tháng).\n- Quy tắc theo iCalendar (kiểu RRULE) khi bạn phải import/export lịch hoặc hỗ trợ nhiều kết hợp.\n\nMột giải pháp trung gian: cho phép tập tùy chọn giới hạn, lưu chúng trong cột, và coi bất kỳ chuỗi RRULE như chỉ để trao đổi.\n\nVí dụ, một quy tắc ca hàng tuần có thể biểu diễn bằng các trường như:\n\n- freq (daily/weekly/monthly) và interval (mỗi N)\n- byweekday (mảng 0-6 hoặc bitmask)\n- tùy chọn bymonthday (1-31) cho quy tắc hàng tháng\n- starts_at_local (ngày+giờ địa phương người dùng chọn) và tzid\n- tùy chọn until_date hoặc count (tránh hỗ trợ cả hai trừ khi cần)\n\nVề giới hạn, ưu tiên lưu duration (ví dụ 8 giờ) thay vì lưu end timestamp cho mọi lần. Duration ổn định khi đồng hồ dịch. Bạn vẫn có thể tính end cho mỗi lần: start + duration.\n\nKhi mở rộng quy tắc, giữ an toàn và có giới hạn:\n\n- Chỉ mở rộng trong window_start và window_end.\n- Thêm đệm nhỏ (ví dụ 1 ngày) cho sự kiện qua đêm.\n- Dừng sau số instance tối đa (ví dụ 500).\n- Lọc ứng viên trước (bằng tzid, freq, và ngày bắt đầu) trước khi sinh.\n\n## Các bước: xây dựng lịch lặp an toàn với DST\n\nMẫu đáng tin cậy là: xử lý từng lần như ý định theo lịch địa phương đầu tiên (ngày + giờ địa phương + múi giờ địa điểm), rồi chỉ chuyển thành instant khi bạn cần sắp xếp, kiểm tra xung đột, hoặc hiển thị.\n\n### 1) Lưu ý định địa phương, không đoán UTC\n\nLưu tên múi giờ của lịch (IANA như America/New_York) cùng giờ bắt đầu địa phương (ví dụ 09:00). Giờ địa phương này là điều doanh nghiệp muốn, ngay cả khi DST dịch chuyển.\n\nCũng lưu duration và ranh giới rõ ràng cho quy tắc: ngày bắt đầu, và hoặc ngày kết thúc hoặc số lần lặp. Ranh giới ngăn lỗi “mở rộng vô hạn”.\n\n### 2) Mô hình ngoại lệ và override riêng\n\nDùng hai bảng nhỏ: một cho ngày bị bỏ, một cho các lần thay đổi. Khóa theo schedule_id + local_date để bạn khớp lần lặp gốc rõ ràng.\n\nKhuôn mẫu thực tế như sau:\n\nsql\n-- core schedule\n-- tz is the location time zone\n-- start_time is local wall-clock time\nschedule(id, tz text, start_date date, end_date date, start_time time, duration_mins int, by_dow int[])\n\nschedule_skip(schedule_id, local_date date)\n\nschedule_override(schedule_id, local_date date, new_start_time time, new_duration_mins int)\n\n\n### 3) Chỉ mở rộng trong cửa sổ yêu cầu\n\nSinh các ngày địa phương ứng viên cho phạm vi bạn đang render (tuần, tháng). Lọc theo ngày trong tuần, rồi áp skips và overrides.\n\nsql\nWITH days AS (\n SELECT d::date AS local_date\n FROM generate_series($1::date, $2::date, interval '1 day') d\n), base AS (\n SELECT s.id, s.tz, days.local_date,\n make_timestamp(extract(year from days.local_date)::int,\n extract(month from days.local_date)::int,\n extract(day from days.local_date)::int,\n extract(hour from s.start_time)::int,\n extract(minute from s.start_time)::int, 0) AS local_start\n FROM schedule s\n JOIN days ON days.local_date BETWEEN s.start_date AND s.end_date\n WHERE extract(dow from days.local_date)::int = ANY (s.by_dow)\n)\nSELECT b.id,\n (b.local_start AT TIME ZONE b.tz) AS start_utc\nFROM base b\nLEFT JOIN schedule_skip sk\n ON sk.schedule_id = b.id AND sk.local_date = b.local_date\nWHERE sk.schedule_id IS NULL;\n\n\n### 4) Chuyển cho người xem ở bước cuối cùng\n\nGiữ start_utc là timestamptz để sắp xếp, kiểm tra xung đột, và đặt chỗ. Chỉ khi hiển thị, chuyển sang múi của người xem. Điều này tránh bất ngờ DST và giữ chế độ xem lịch nhất quán.\n\n## Mẫu truy vấn để sinh chế độ xem lịch đúng\n\nMàn hình lịch thường là truy vấn phạm vi: “hiển thị mọi thứ giữa from_ts và to_ts.” Mẫu an toàn là:\n\n1) Chỉ mở rộng các ứng viên trong cửa sổ đó.\n2) Áp ngoại lệ/override.\n3) Trả ra hàng cuối cùng với start_at và end_at là timestamptz.\n\n### Mở rộng hàng ngày hoặc hàng tuần với generate_series\n\nVới các quy tắc hàng tuần đơn giản (như “mỗi Thứ Hai–Thứ Sáu lúc 09:00 theo local”), sinh ngày địa phương trong múi của schedule, rồi biến mỗi ngày+giờ địa phương thành một instant.\n\nsql\n-- Inputs: :from_ts, :to_ts are timestamptz\n-- rule.tz is an IANA zone like 'America/New_York'\nWITH bounds AS (\n SELECT\n (:from_ts AT TIME ZONE rule.tz)::date AS from_local_date,\n (:to_ts AT TIME ZONE rule.tz)::date AS to_local_date\n FROM rule\n WHERE rule.id = :rule_id\n), days AS (\n SELECT d::date AS local_date\n FROM bounds, generate_series(from_local_date, to_local_date, interval '1 day') AS g(d)\n)\nSELECT\n (local_date + rule.start_local_time) AT TIME ZONE rule.tz AS start_at,\n (local_date + rule.end_local_time) AT TIME ZONE rule.tz AS end_at\nFROM rule\nJOIN days ON true\nWHERE EXTRACT(ISODOW FROM local_date) = ANY(rule.by_isodow);\n\n\nCách này hoạt động tốt vì việc chuyển thành timestamptz xảy ra cho từng lần, nên các dịch chuyển DST áp dụng đúng ngày đó.\n\n### Các quy tắc phức tạp hơn với CTE đệ quy\n\nKhi quy tắc phụ thuộc vào “thứ N của tuần”, khoảng cách, hoặc khoảng tùy chỉnh, CTE đệ quy có thể sinh lần kế tiếp liên tiếp cho đến khi vượt to_ts. Giữ đệ quy neo trong cửa sổ để không chạy mãi mãi.\n\nSau khi có các hàng ứng viên, áp overrides và hủy bằng cách join các bảng ngoại lệ trên (rule_id, start_at) hoặc khóa địa phương như (rule_id, local_date). Nếu có bản hủy, loại row đó. Nếu có override, thay start_at/end_at bằng giá trị override.\n\nCác mẫu hiệu năng quan trọng nhất:\n\n- Hạn chế phạm vi sớm: lọc quy tắc trước, rồi chỉ mở rộng trong [from_ts, to_ts).\n- Index các bảng ngoại lệ/override theo (rule_id, start_at) hoặc (rule_id, local_date).\n- Tránh mở rộng nhiều năm dữ liệu cho chế độ xem tháng.\n- Cache các lần mở rộng chỉ nếu bạn có thể invalidate sạch khi quy tắc thay đổi.\n\n## Xử lý ngoại lệ và override một cách rõ ràng\n\nLịch lặp chỉ hữu dụng nếu bạn có thể phá vỡ nó an toàn. Trong app booking và ca làm việc, “tuần bình thường” là quy tắc gốc, mọi thứ khác là ngoại lệ: ngày lễ, hủy, cuộc hẹn di chuyển, hoặc đổi ca. Nếu ngoại lệ được gắn thêm sau, chế độ xem lịch sẽ trôi và xuất hiện bản sao.\n\nGiữ ba khái niệm tách biệt:\n\n- Schedule cơ sở (quy tắc lặp và múi giờ)\n- Skips (ngày hoặc instance phải không xảy ra)\n- Overrides (một lần tồn tại nhưng có thông tin thay đổi)\n\n### Dùng thứ tự ưu tiên cố định\n\nChọn một thứ tự và giữ nhất quán. Một lựa chọn phổ biến:\n\n1) Sinh ứng viên từ quy tắc gốc.\n2) Áp overrides (thay row sinh).\n3) Áp skips (ẩn nó).\n\nHãy đảm bảo quy tắc dễ giải thích cho người dùng trong một câu.\n\n### Tránh trùng khi override thay thế một instance\n\nTrùng thường xảy ra khi truy vấn trả cả occurrence sinh lẫn row override. Ngăn bằng khóa ổn định:\n\n- Đặt mỗi instance sinh một khóa ổn định, như (schedule_id, local_date, start_time, tzid).\n- Lưu khóa đó trên row override như “original occurrence key.”\n- Thêm ràng buộc unique để chỉ có một override cho mỗi occurrence gốc.\n\nRồi, trong truy vấn, loại trừ occurrence sinh có override khớp và union vào các row override.\n\n### Giữ khả năng audit mà không gây phiền phức\n\nNgoại lệ là nơi tranh chấp xảy ra (“Ai đã đổi ca của tôi?”). Thêm các trường audit cơ bản trên skips và overrides: created_by, created_at, updated_by, updated_at, và lý do tùy chọn.\n\n## Những sai lầm thường gây lỗi lệch một giờ\n\nHầu hết lỗi một giờ đến từ việc trộn hai nghĩa của thời gian: instant (một điểm trên timeline UTC) và giờ trên đồng hồ địa phương (như 09:00 mỗi Thứ Hai ở New York).\n\nSai lầm kinh điển là lưu một quy tắc giờ địa phương dưới dạng timestamptz. Nếu bạn lưu “Thứ Hai lúc 09:00 America/New_York” như một timestamptz, bạn đã chọn một ngày cụ thể (và trạng thái DST). Sau này, khi sinh các Thứ Hai tiếp theo, ý định ban đầu (“luôn 09:00 địa phương”) bị mất.\n\nMột nguyên nhân thường gặp khác là dựa vào offset cố định như -05:00 thay vì tên vùng IANA. Offset không có quy tắc DST. Lưu ID vùng (ví dụ America/New_York) và để PostgreSQL áp quy tắc đúng cho từng ngày.\n\nCẩn thận thời điểm bạn chuyển đổi. Nếu chuyển sang UTC quá sớm khi sinh một recurrence, bạn có thể đóng băng offset DST và áp nó cho mọi lần. Mẫu an toàn hơn là: sinh occurrence theo điều kiện địa phương (ngày + giờ + zone), rồi chuyển mỗi occurrence thành instant.\n\nNhững sai lầm lặp lại:\n\n- Dùng timestamptz để lưu một giờ địa phương lặp (bạn cần time + tzid + quy tắc).\n- Chỉ lưu offset, không lưu tên vùng IANA.\n- Chuyển đổi trong khi sinh recurrence thay vì ở cuối.\n- Mở rộng recurrences “mãi mãi” không giới hạn thời gian.\n- Không test cả tuần bắt đầu DST và tuần kết thúc DST.\n\nMột test đơn giản bắt lỗi hầu hết: chọn một vùng có DST, tạo ca hàng tuần 09:00, và render lịch hai tháng băng qua thay đổi DST. Xác nhận mọi lần đều hiển thị 09:00 địa phương, dù instant UTC khác nhau.\n\n## Danh sách kiểm tra nhanh trước khi phát hành\n\nTrước khi release, kiểm tra những cơ bản:\n\n- Mỗi lịch gắn vào một địa điểm (hoặc đơn vị kinh doanh) với tên múi giờ, lưu trên schedule.\n- Bạn lưu ID vùng IANA (như America/New_York), không phải offset thô.\n- Việc mở rộng recurrence chỉ sinh trong phạm vi yêu cầu.\n- Ngoại lệ và overrides có một thứ tự ưu tiên duy nhất, được ghi tài liệu.\n- Bạn test tuần thay đổi DST và người xem ở múi khác so với lịch.\n\nLàm một dry run thực tế: một cửa hàng ở Europe/Berlin có ca hàng tuần lúc 09:00 giờ địa phương. Một quản lý xem từ America/Los_Angeles. Xác nhận ca vẫn là 09:00 Berlin mỗi tuần, ngay cả khi mỗi vùng thay đổi DST vào ngày khác nhau.\n\n## Ví dụ: ca nhân viên hàng tuần với ngày lễ và thay đổi DST\n\nMột phòng khám nhỏ có một ca lặp: mỗi Thứ Hai, 09:00–17:00 theo múi địa phương của phòng khám (America/New_York). Phòng khám đóng vào một Thứ Hai cụ thể vì ngày lễ. Một nhân viên đi châu Âu hai tuần, nhưng lịch phòng khám phải neo theo giờ đồng hồ của phòng khám, không phải vị trí nhân viên.\n\nĐể hành vi đúng:\n\n- Lưu quy tắc lặp neo theo ngày địa phương (weekday = Monday, giờ địa phương = 09:00–17:00).\n- Lưu múi giờ lịch (America/New_York).\n- Lưu ngày bắt đầu hiệu lực để quy tắc có mốc rõ.\n- Lưu một ngoại lệ để hủy Thứ Hai ngày lễ (và overrides cho thay đổi một lần).\n\nBây giờ render lịch hai tuần bao gồm thay đổi DST ở New York. Truy vấn sinh các Thứ Hai trong phạm vi ngày địa phương đó, gắn giờ địa phương của phòng khám, rồi chuyển mỗi lần thành instant (timestamptz). Vì chuyển đổi xảy ra cho từng lần, DST được xử lý đúng ngày.\n\nNgười xem khác nhau thấy giờ đồng hồ khác nhau cho cùng một instant:\n\n- Quản lý ở Los Angeles thấy nó sớm hơn trên đồng hồ.\n- Nhân viên đi Berlin thấy nó muộn hơn trên đồng hồ.\n\nPhòng khám vẫn đạt mục tiêu: 09:00–17:00 giờ New York, mỗi Thứ Hai không bị hủy.\n\n## Các bước tiếp theo: triển khai, test và giữ dễ bảo trì\n\nKhoá chặt cách tiếp cận thời gian sớm: bạn sẽ lưu chỉ quy tắc, chỉ occurrence, hay hybrid? Với nhiều sản phẩm booking và ca, hybrid là hợp lý: giữ quy tắc là nguồn chân lý, lưu cache lăn nếu cần, và lưu ngoại lệ/override dưới dạng hàng cụ thể.\n\nViết một “hợp đồng thời gian” ở một chỗ: cái gì tính là instant, cái gì là giờ đồng hồ địa phương, và cột nào lưu mỗi loại. Điều này ngăn drift khi một endpoint trả giờ địa phương trong khi endpoint khác trả UTC.\n\nGiữ việc sinh recurrence là một module, không rải khắp SQL. Nếu bạn thay đổi cách hiểu “09:00 AM local”, bạn muốn chỉ cập nhật ở một nơi.\n\nNếu bạn xây công cụ lập lịch mà không viết tay mọi thứ, AppMaster (appmaster.io) là một lựa chọn thiết thực cho công việc này: bạn có thể mô hình hóa database trong Data Designer, xây logic recurrence và ngoại lệ trong business processes, và vẫn có backend và mã app thật sự để chạy sản xuất.