PostgreSQL에서 반복 일정과 시간대: 패턴

시간대와 반복 이벤트가 잘못되는 이유

대부분의 캘린더 버그는 수학적 오류가 아닙니다. 의미상의 오류입니다. 한 가지(특정 시점)를 저장했는데, 사용자는 다른 것(특정 장소의 로컬 시각)을 기대합니다. 이 차이가 반복 일정과 시간대를 테스트에서는 괜찮아 보이게 했다가 실제 사용자가 나타나면 깨지는 이유입니다.

일광절약시간(DST)은 전형적인 트리거입니다. “매주 일요일 09:00”이라는 표현은 “시작 타임스탬프에서 7일마다”와 같지 않습니다. 오프셋이 바뀌면 두 개념은 한 시간씩 어긋나고 캘린더는 조용히 틀어집니다.

여행과 혼합된 시간대는 또 다른 문제를 더합니다. 예약은 물리적 장소(예: 시카고의 미용실 좌석)에 묶여 있는데, 이를 보는 사람은 런던에 있을 수 있습니다. 장소 기반 스케줄을 사람 기반으로 처리하면 적어도 한쪽에는 잘못된 로컬 시간이 보여집니다.

일반적인 실패 모드:

• 저장된 타임스탬프에 간격을 더해 발생을 생성했는데 DST가 바뀝니다.
• 시간대 규칙 없이 "로컬 시간"만 저장해 나중에 의도한 시점을 재구성할 수 없습니다.
• DST 경계를 넘지 않는 날짜만 테스트합니다.
• 하나의 쿼리에서 “이벤트 시간대”, “사용자 시간대”, “서버 시간대”를 섞어 사용합니다.

스키마를 선택하기 전에, 제품에서 “정확함”이 무엇을 의미하는지 결정하세요.

예약의 경우 “정확함”은 보통: 약속이 장소의 시간대에서 의도된 벽시계 시간에 발생하고, 이를 보는 모든 사람에게 올바르게 변환되어 보이는 것입니다.

교대 근무의 경우 “정확함”은 종종: 직원이 여행 중이더라도 근무는 매장 고정의 로컬 시간에 시작되는 것입니다.

장소에 묶을지 사람에 묶을지 하는 그 한 가지 결정이 나머지를 모두 좌우합니다: 무엇을 저장할지, 어떻게 반복을 생성할지, 그리고 한 시간 차이의 놀라움 없이 캘린더 뷰를 어떻게 조회할지.

올바른 사고 모델 선택: 인스턴트 vs 로컬 시간

많은 버그는 두 가지 시간 개념을 섞어서 발생합니다:

• 인스턴트: 절대적인 한 순간. 한 번 발생합니다.
• 로컬 시간 규칙: “파리에서 매주 월요일 오전 9시” 같은 벽시계 시간.

인스턴트는 어디서나 동일합니다. “2026-03-10 14:00 UTC”는 인스턴트입니다. 화상 통화, 항공편 출발, “정확히 이 순간에 알림을 보낸다”는 보통 인스턴트입니다.

로컬 시간은 특정 장소의 시계에서 읽히는 시간입니다. “Europe/Paris에서 평일마다 오전 9시”는 로컬 시간입니다. 영업시간, 반복 수업, 직원 교대는 보통 장소의 시간대에 고정됩니다. 시간대는 단순한 표시 선호가 아니라 의미의 일부입니다.

간단한 경험 법칙:

• 이벤트가 전 세계에서 하나의 실제 순간에 일어나야 하면 시작/종료를 인스턴트로(timestamptz) 저장하세요.
• 이벤트가 한 장소의 시계에 따라야 하면 로컬 날짜와 로컬 시간에 시간대 ID를 함께 저장하세요. 발생을 생성할 때만 이를 실제 인스턴트로 변환하세요.
• 사용자가 이동 중이면 뷰어의 시간대로 표시하되, 스케줄은 그 장소에 고정하세요.
• +02:00 같은 오프셋으로 시간대를 추정하지 마세요. 오프셋은 DST 규칙을 포함하지 않습니다.

예: 병원 교대는 “Mon-Fri 09:00-17:00 America/New_York”입니다. DST가 바뀌는 주에도 교대는 현지 기준으로 여전히 9시부터 5시입니다. 다만 UTC 인스턴트는 한 시간씩 이동합니다.

PostgreSQL에서 중요한 타입(그리고 피할 것)

대부분의 캘린더 버그는 잘못된 컬럼 타입에서 시작합니다. 핵심은 실제 순간과 벽시계 기대를 분리하는 것입니다.

실제 인스턴트에는 timestamptz를 사용하세요: 예약, 출퇴근 기록, 알림, 사용자나 지역 간 비교가 필요한 것들. PostgreSQL은 이를 절대 시점으로 저장하고 표시할 때 변환하므로 정렬과 중복 검사에서 기대한 동작을 합니다.

로컬 벽시계 값에는 timestamp without time zone(또는 단순히 time/date 조합)을 사용하세요. 예: “매주 월요일 09:00” 같은 값은 자체로 인스턴트가 아니므로 시간대 식별자와 쌍으로 저장하고 발생을 생성할 때만 인스턴트로 변환합니다.

반복 패턴에는 기본 타입들이 도움이 됩니다:

• date는 날짜 단위 예외(공휴일)에 유용
• time은 일별 시작 시간
• interval은 지속 시간(예: 6시간 교대)

시간대는 IANA 이름(예: America/New_York)으로 text 컬럼에 저장하세요(또는 작은 룩업 테이블). -0500 같은 오프셋은 DST 규칙을 포함하지 않으므로 충분하지 않습니다.

많은 앱에 실용적인 세트:

• 예약된 약속의 시작/종료 인스턴트는 timestamptz
• 예외일은 date
• 반복 로컬 시작 시간은 time
• 지속 시간은 interval
• IANA 시간대 ID는 text

예약 및 교대 앱을 위한 데이터 모델 옵션

최고의 스키마는 스케줄이 얼마나 자주 바뀌는지와 사용자가 얼마나 멀리 앞을 조회하는지에 따라 다릅니다. 보통은 처음에 많은 행을 쓰거나 읽을 때 생성하는 것 중 하나를 선택하게 됩니다.

옵션 A: 모든 발생을 저장하기

확장된 각 교대나 예약마다 한 행을 삽입합니다. 쿼리가 쉽고 이해하기 쉽습니다. 대신 쓰기 비용이 크고 규칙이 바뀌면 많은 업데이트가 필요합니다.

이 방식은 이벤트가 대부분 일회성이거나, 앞으로 짧은 기간(예: 다음 30일)만 생성할 경우 잘 맞습니다.

옵션 B: 규칙을 저장하고 읽을 때 확장하기

스케줄 규칙(예: "America/New_York에서 매주 월·수 오전 9시")을 저장하고 요청된 범위에서 발생을 온디맨드로 생성합니다.

유연하고 저장 공간이 적게 들지만, 쿼리가 더 복잡해집니다. 월별 보기에서는 느려질 수 있으므로 캐시가 필요할 수 있습니다.

옵션 C: 규칙 + 캐시된 발생(하이브리드)

규칙을 진실의 근원으로 두고, 롤링 윈도우(예: 60-90일) 동안 생성된 발생을 저장합니다. 규칙이 바뀌면 캐시를 다시 생성합니다.

이 방법은 교대 앱에 좋은 기본값입니다: 월뷰는 빠르게 유지되지만 패턴을 편집할 한 곳이 남아 있습니다.

실용적인 테이블 세트:

• schedule: 소유자/리소스, 시간대, 로컬 시작 시간, 지속 시간, 반복 규칙
• occurrence: start_at timestamptz, end_at timestamptz와 상태를 포함한 확장된 인스턴스
• exception: "이 날짜는 건너뜀" 또는 "이 날짜는 다름" 표시자
• override: 특정 발생에 대한 수정(변경된 시작 시간, 교대 교체, 취소 플래그)
• (선택) schedule_cache_state: 마지막 생성된 범위로 다음에 채워야 할 범위를 알 수 있음

캘린더 범위 쿼리용 인덱스:

• occurrence: btree (resource_id, start_at) 및 종종 btree (resource_id, end_at)
• 범위 중첩(overlaps) 쿼리가 많다면: tstzrange(start_at, end_at) 생성 컬럼과 gist 인덱스

규칙을 취약하게 만들지 않고 반복 규칙 표현하기

반복 스케줄은 규칙이 지나치게 복잡하거나 유연하거나 쿼리 불가능한 블롭으로 저장될 때 깨집니다. 좋은 규칙 형식은 앱에서 검증할 수 있고 팀이 빠르게 설명할 수 있는 것입니다.

두 가지 일반적 접근:

• 실제로 지원하는 패턴에 대해 간단한 커스텀 필드를 사용(주간 교대, 월간 청구일 등)
• 많은 조합을 지원하거나 캘린더를 가져오고 내보내야 하면 iCalendar 스타일(RRULE) 사용

실용적인 절충안: 제한된 옵션을 허용하고 컬럼에 저장하되 RRULE 문자열은 교환용으로만 취급하세요.

예: 주간 교대 규칙은 다음 필드로 표현할 수 있습니다:

• freq(daily/weekly/monthly)와 interval(매 N회)
• byweekday(0-6 배열 또는 비트마스크)
• 월간 규칙을 위한 선택적 bymonthday(1-31)
• 사용자가 선택한 starts_at_local(로컬 날짜+시간)과 tzid
• 선택적 until_date 또는 count(두 가지를 모두 지원하지 않는 것을 권장)

경계는 **지속 시간(duration)**을 저장하는 것이 좋습니다(예: 8시간) — 각 발생의 종료 타임스탬프를 매번 저장하는 대신. 지속 시간은 시계가 이동해도 안정적입니다. 발생별 종료 시간은: 시작 + 지속 시간으로 계산할 수 있습니다.

규칙을 확장할 때는 안전하고 한정된 범위로 하세요:

• window_start와 window_end 내에서만 확장합니다.
• 밤샘 이벤트를 위해 작은 버퍼(예: 1일)를 추가합니다.
• 최대 인스턴스 수(예: 500) 이후 중단합니다.
• 생성 전에 후보를 먼저 필터링(예: tzid, freq, 시작 날짜)합니다.

단계별: DST 안전한 반복 스케줄 구축하기

신뢰할 수 있는 패턴은: 각 발생을 먼저 로컬 캘린더 의도(날짜 + 로컬 시간 + 장소의 시간대)로 취급하고, 정렬, 충돌 검사 또는 표시가 필요할 때만 인스턴트로 변환하는 것입니다.

1) UTC 추측이 아닌 로컬 의도를 저장하세요

스케줄의 위치 시간대(IANA 이름, 예: America/New_York)와 로컬 시작 시간(예: 09:00)을 저장하세요. 이 로컬 시간은 비즈니스가 의미하는 바이며 DST가 바뀌어도 유지됩니다.

또한 지속 시간과 명확한 경계(시작 날짜와 종료 날짜 또는 반복 횟수)를 저장하세요. 경계는 "무한 확장" 버그를 방지합니다.

2) 예외와 오버라이드를 별도로 모델링하세요

건너뛸 날짜와 변경된 발생을 위한 두 개의 작은 테이블을 사용하세요. schedule_id + local_date로 키를 지정하면 원래 반복과 정확히 매칭할 수 있습니다.

실용적 구조 예:

-- 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) 요청된 윈도우 안에서만 확장하세요

렌더링할 범위(주, 월 등)의 로컬 날짜 후보만 생성합니다. 요일로 필터링한 뒤 건너뛰기와 오버라이드를 적용하세요.

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) 변환은 맨 마지막에, 뷰어용으로만 하세요

정렬, 충돌 검사, 예약에는 start_utc를 timestamptz로 유지하세요. 표시할 때만 뷰어의 시간대로 변환하면 DST 놀라움을 피하고 캘린더 뷰를 일관되게 유지할 수 있습니다.

올바른 캘린더 뷰를 생성하는 쿼리 패턴

캘린더 화면은 보통 범위 쿼리입니다: “from_ts와 to_ts 사이의 모든 것을 보여줘.” 안전한 패턴은:

1. 해당 윈도우의 후보만 확장합니다.
2. 예외/오버라이드를 적용합니다.
3. 최종 행을 start_at와 end_at을 timestamptz로 출력합니다.

generate_series로 일간 또는 주간 확장

간단한 주간 규칙(예: "매주 월~금 로컬 오전 9시")에는 스케줄의 시간대에서 로컬 날짜를 생성한 다음, 각 로컬 날짜 + 로컬 시간을 인스턴트로 변환하세요.

-- 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);

이 방법은 발생마다 timestamptz로 변환하므로 DST 변화가 해당 날짜에 올바르게 적용됩니다.

"n번째 요일" 같은 복잡한 규칙에는 재귀 CTE

규칙이 "n번째 요일"이나 갭, 커스텀 간격에 의존하면 재귀 CTE로 다음 발생을 반복 생성해 to_ts를 지날 때까지 만들 수 있습니다. 반드시 윈도우에 고정하여 무한 루프를 방지하세요.

후보 행을 얻으면 (rule_id, start_at) 또는 (rule_id, local_date) 같은 로컬 키로 예외와 취소를 조인해 적용하세요. 취소 레코드가 있으면 행을 제거하고, 오버라이드가 있으면 start_at/end_at을 오버라이드 값으로 교체합니다.

성능 관련 패턴:

• 초기에 범위를 제한하세요: 규칙을 먼저 필터링한 뒤 [from_ts, to_ts) 내에서만 확장합니다.
• 예외/오버라이드 테이블은 (rule_id, start_at) 또는 (rule_id, local_date)에 인덱스하세요.
• 월뷰를 위해 수년치를 확장하지 마세요.
• 규칙이 바뀔 때 깔끔하게 무효화할 수 있다면 확장된 발생을 캐시하세요.

예외와 오버라이드를 깔끔하게 처리하기

반복 스케줄은 안전하게 깨질 수 있어야 실용적입니다. 예약과 교대 앱에서 “정상” 주는 기본 규칙이고, 나머지는 예외입니다: 공휴일, 취소, 이동된 약속, 직원 교체 등. 예외를 나중에 덧붙이면 캘린더 뷰가 어긋나거나 중복이 생깁니다.

세 가지 개념을 분리하세요:

• 기본 스케줄(반복 규칙과 그 시간대)
• 건너뛰기(발생하지 말아야 할 날짜/인스턴스)
• 오버라이드(발생은 존재하지만 세부가 변경됨)

고정된 우선순위 사용

한 가지 순서를 정하고 일관되게 유지하세요. 일반적인 선택:

1. 기본 반복에서 후보 생성
2. 오버라이드 적용(생성된 것을 대체)
3. 건너뛰기 적용(숨김)

사용자에게 한 문장으로 설명하기 쉬운 규칙이어야 합니다.

오버라이드가 인스턴스를 대체할 때 중복 방지

중복은 쿼리가 생성된 발생과 오버라이드 행을 둘 다 반환할 때 자주 발생합니다. 안정적 키로 방지하세요:

• 각 생성된 인스턴스에 (schedule_id, local_date, start_time, tzid) 같은 안정 키를 부여하세요.
• 오버라이드 행에 이 키를 "원래 발생 키"로 저장하세요.
• 기본 발생당 하나의 오버라이드만 존재하도록 고유 제약을 추가하세요.

그런 다음 쿼리에서 매칭되는 오버라이드가 있는 생성된 발생은 제외하고 오버라이드 행을 합치면 중복을 피할 수 있습니다.

감사 가능성 유지

예외는 분쟁이 발생하기 쉬운 곳입니다("누가 내 교대를 바꿨지?"). skips와 overrides에 기본 감사 필드(created_by, created_at, updated_by, updated_at, 선택적 이유)를 추가하세요.

한 시간 오차 버그를 일으키는 흔한 실수

대부분의 한 시간 버그는 인스턴트(UTC 타임라인의 점)와 로컬 시계 읽기(예: 뉴욕의 매주 월요일 09:00)를 혼동하는 데서 옵니다.

전형적인 실수는 로컬 벽시계 규칙을 timestamptz로 저장하는 것입니다. "뉴욕의 월요일 오전 9시"를 단일 timestamptz로 저장하면 이미 특정 날짜(및 그때의 DST 상태)를 선택한 셈이 됩니다. 이후 미래의 월요일을 생성할 때 원래 의도("항상 로컬 09:00")는 사라집니다.

또 다른 빈번한 원인은 -05:00 같은 고정 UTC 오프셋에 의존하는 것입니다. 오프셋은 DST 규칙을 포함하지 않습니다. IANA 존 이름(예: America/New_York)을 저장하고 PostgreSQL이 각 날짜에 맞는 규칙을 적용하게 하세요.

변환 시점을 조심하세요. 반복 생성 중에 너무 일찍 UTC로 변환하면 DST 오프셋을 고정해 매 발생에 적용할 수 있습니다. 더 안전한 패턴은: 발생을 로컬(날짜 + 로컬 시간 + 존)으로 생성한 뒤 각 발생을 인스턴트로 변환하는 것입니다.

자주 반복되는 실수 목록:

• 반복 로컬 시간대를 저장해야 할 때 timestamptz를 사용함(이럴 때는 time + tzid + 규칙이 필요)
• IANA 존이 아니라 오프셋만 저장함
• 반복 생성 중에 변환을 수행함이 아니라 마지막에 변환함
• 무한히 확장되는 반복을 하드한 시간 윈도우 없이 확장함
• DST 시작 주와 종료 주를 테스트하지 않음

대부분의 문제를 잡는 간단한 테스트: DST가 있는 존을 골라 주간 09:00 교대를 만들고 DST 변경을 포함한 두 달짜리 캘린더를 렌더링하세요. 각 인스턴스가 로컬 기준으로 매번 09:00으로 보이는지 확인하세요(기초가 되는 UTC 인스턴트는 달라집니다).

출시 전에 확인할 빠른 체크리스트

출시 전에 기본을 점검하세요:

• 모든 스케줄이 명명된 시간대와 연계되어 있는가(스케줄 자체에 저장)?
• IANA 존 ID(예: America/New_York)를 저장하고 있는가, 원시 오프셋이 아닌가?
• 반복 확장은 요청된 범위 내에서만 이루어지는가?
• 예외와 오버라이드에 단일 문서화된 우선순위가 있는가?
• DST 변경 주와 스케줄 시간대와 다른 시간대의 뷰어를 테스트했는가?

현실적인 드라이런을 하나 해보세요: Europe/Berlin의 매주 09:00 교대가 있고 매니저는 America/Los_Angeles에서 봅니다. 각 주마다 교대가 베를린 시간으로 09:00으로 유지되는지, 각 지역의 DST가 다르게 바뀌어도 유지되는지 확인하세요.

예시: 공휴일과 DST 변경이 포함된 주간 직원 교대

작은 클리닉이 매주 월요일 09:00~17:00 America/New_York 현지 시간으로 반복 교대를 운영합니다. 한 주의 월요일은 공휴일이라 문을 닫습니다. 한 직원은 2주간 유럽으로 이동 중이지만 스케줄은 직원의 위치가 아니라 클리닉의 벽시계에 묶여 있어야 합니다.

올바르게 동작하게 하려면:

• 요일 = 월요일, 로컬 시간 = 09:00~17:00 같은 로컬 날짜에 고정된 반복 규칙을 저장하세요.
• 스케줄 시간대(America/New_York)를 저장하세요.
• 규칙에 명확한 기준점이 되도록 유효 시작 날짜를 저장하세요.
• 공휴일 월요일을 취소하는 예외(및 일회성 변경을 위한 오버라이드)를 저장하세요.

이제 DST 변경을 포함하는 2주 범위를 렌더링하면, 쿼리는 해당 로컬 날짜 범위의 월요일들을 생성하고 클리닉의 로컬 시간을 붙인 뒤 각 발생을 절대 인스턴트(timestamptz)로 변환합니다. 변환이 발생마다 일어나므로 DST는 올바른 날짜에 처리됩니다.

서로 다른 뷰어는 동일한 인스턴트에 대해 서로 다른 로컬 시각을 봅니다:

• 로스앤젤레스의 매니저는 시계상 더 이른 시간으로 봅니다.
• 베를린에 있는 이동 중인 직원은 더 늦은 시간으로 봅니다.

그럼에도 클리닉은 원한 것을 얻습니다: 매주 공휴일이 아닌 월요일마다 뉴욕 시간으로 09:00~17:00.

다음 단계: 구현, 테스트, 유지보수성 확보

시간 접근 방식을 조기에 고정하세요: 규칙만 저장할지, 발생만 저장할지, 아니면 하이브리드일지 결정하세요. 많은 예약·교대 제품에는 하이브리드가 잘 맞습니다: 규칙을 진실의 근원으로 두고 필요하면 롤링 캐시를 저장하며 예외와 오버라이드는 구체적 행으로 저장합니다.

한 곳에 "시간 계약(time contract)"을 문서화하세요: 무엇이 인스턴트인지, 무엇이 로컬 벽시계인지, 각 값을 어느 컬럼에 저장하는지. 이렇게 하면 한 엔드포인트는 로컬 시간을 반환하고 다른 엔드포인트는 UTC를 반환하는 상황을 방지할 수 있습니다.

반복 생성 로직은 하나의 모듈로 유지하세요. 그래야 "로컬 오전 9시"를 해석하는 방법을 바꿀 때 한 곳만 업데이트하면 됩니다.

모든 것을 손으로 코딩하지 않고 스케줄링 도구를 만들고 있다면 AppMaster (appmaster.io)은 이런 작업에 실용적인 선택입니다: Data Designer에서 데이터베이스를 모델링하고, 시각적 비즈니스 프로세스에서 반복과 예외 로직을 구성하면 실제로 생성 가능한 백엔드와 앱 코드를 얻을 수 있습니다.