Повторяющиеся расписания и часовые пояса в PostgreSQL: шаблоны

Почему с часовыми поясами и повторяющимися событиями случаются ошибки

Большинство ошибок календаря — это не арифметические ошибки. Это ошибки смысла. Вы сохраняете одно (момент времени), а пользователи ожидают другое (местное время на часах в конкретном месте). Этот разрыв — причина того, почему повторяющиеся расписания и часовые пояса могут выглядеть правильно в тестах, а потом ломаются при реальных пользователях.

Переход на летнее/зимнее время (DST) — классический триггер. Сдвиг «каждое воскресенье в 09:00» не то же самое, что «каждые 7 дней от начальной метки времени». Когда смещение меняется, эти два представления расходятся на час, и ваш календарь тихо становится неправильным.

Путешествия и смешанные часовые пояса добавляют ещё уровень сложности. Бронирование может быть привязано к физическому месту (кресло в салоне в Чикаго), а человек, который его просматривает — в Лондоне. Если вы будете трактовать расписание, привязанное к месту, как расписание, привязанное к человеку, минимум одна сторона увидит неверное местное время.

Распространённые сценарии отказа:

• Вы генерируете повторения, прибавляя интервал к сохранённой метке времени, а потом меняется DST.
• Вы сохраняете «местное время» без правил часового пояса, и потом нельзя восстановить задуманное мгновение.
• Вы тестируете только даты, которые никогда не пересекают границу DST.
• Вы смешиваете «часовой пояс события», «часовой пояс пользователя» и «часовой пояс сервера» в одном запросе.

Прежде чем выбирать схему, решите, что для вашего продукта значит «правильно».

Для бронирования «правильно» обычно значит: приём проходит в задуманное время по часам в часовом поясе места, и все, кто его просматривает, видят корректное преобразование.

Для смены «правильно» часто значит: смена начинается в фиксированное местное время для магазина, даже если сотрудник в поездке.

Это одно решение (привязанность расписания к месту или к человеку) определяет всё остальное: что вы храните, как генерируете повторения и как запрашиваете календарь без внезапных сдвигов на час.

Выберите правильную модель мышления: мгновение vs местное время

Много ошибок возникает из смешения двух разных представлений времени:

• Мгновение: абсолютный момент, который происходит один раз.
• Правило местного времени: показание часов, например «каждый понедельник в 9:00 по Европе/Париж».

Мгновение одинаково везде. «2026-03-10 14:00 UTC» — это мгновение. Видеозвонки, вылеты и «отправить уведомление в точно этот момент» обычно являются мгновениями.

Местное время — это то, что люди читают на часах в месте. «09:00 по Europe/Paris в будние дни» — это местное время. Часы работы, регулярные занятия и смены сотрудников обычно привязаны к часовому поясу места. Часовой пояс — часть смысла, а не просто настройка отображения.

Простое правило:

• Храните start/end как мгновения (timestamptz), когда событие должно произойти в один и тот же реальный момент по всему миру.
• Храните местную дату и местное время плюс идентификатор зоны, когда событие должно следовать за часами в одном месте.
• Если пользователи в пути, показывайте им времена в часовом поясе зрителя, но сохраняйте расписание, привязанное к своей зоне.
• Не догадывайтесь о зоне по смещениям вроде "+02:00" — смещения не содержат правил DST.

Пример: смена в больнице — «Пн-Пт 09:00–17:00 America/New_York». В неделю перехода на DST смена всё ещё 9–5 по местному времени, хотя UTC-моменты смещаются на час.

Типы PostgreSQL, которые имеют значение (и чего избегать)

Большинство ошибок календаря начинается с неверного типа столбца. Ключ — отделить реальное мгновение от ожидания по часам.

Используйте timestamptz для реальных мгновений: брони, отметок времени входа, уведомлений и всего, что вы сравниваете между пользователями или регионами. PostgreSQL хранит это как абсолютное мгновение и конвертирует при отображении, так что упорядочение и проверки пересечений работают как ожидается.

Используйте timestamp without time zone для локальных показаний времени, которые сами по себе не являются мгновениями, например «каждый понедельник в 09:00» или «магазин открывается в 10:00». Сопоставляйте это с идентификатором часового пояса и конвертируйте в мгновение только при генерации экземпляров.

Для повторяющихся шаблонов базовые типы полезны:

• date для исключений по дням (праздники)
• time для ежедневного времени начала
• interval для длительностей (например, 6-часовая смена)

Храните часовой пояс как имя IANA (например, America/New_York) в столбце text (или в небольшой справочной таблице). Смещения вроде -0500 недостаточны, потому что не включают правила перехода на летнее время.

Практичный набор для многих приложений:

• timestamptz для стартовых/конечных мгновений забронированных встреч
• date для дней-исключений
• time для локального времени старта повторений
• interval для длительности
• text для IANA-идентификатора часовой зоны

Варианты модели данных для приложений бронирования и смен

Лучшая схема зависит от того, как часто меняются расписания и как заблаговременно люди просматривают календарь. Обычно вы выбираете между большим количеством записей при записи или генерацией при чтении.

Вариант A: хранить каждое событие

Вставляйте по одной строке на смену или бронирование (уже развернутое). Это просто для запросов и понимания. Минус — много операций записи и обновлений при изменении правила.

Это хорошо работает, когда события в основном единичные или вы создаёте экземпляры только на небольшой промежуток вперёд (например, на следующие 30 дней).

Вариант B: хранить правило и разворачивать при чтении

Храните правило расписания (например, «еженедельно по Пн и Ср в 09:00 в America/New_York») и генерируйте экземпляры для запрошенного диапазона по требованию.

Это гибко и экономит место, но запросы становятся сложнее. Представления за месяц могут работать медленнее, если не кешировать результаты.

Вариант 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)
• Если часто делаете запросы «пересекается с диапазоном»: сгенерируйте 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 (избегайте поддержки обоих одновременно, если это не нужно)

Для границ предпочтительнее хранить длительность (например, 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

Для простых еженедельных правил (например, «каждый Пн–Пт в 09:00 локально») генерируйте локальные даты в часовом поясе расписания, затем превращайте каждую локальную дату + локальное время в мгновение.

-- 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 применяются в нужный день.

Более сложные правила с рекурсивным 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).
• Храните этот ключ в строке переопределения как «ключ оригинального экземпляра».
• Добавьте уникальное ограничение, чтобы на один базовый экземпляр было не более одного переопределения.

Затем в запросах исключайте сгенерированные экземпляры, имеющие соответствующее переопределение, и объединяйте результаты с переопределёнными строками.

Сохраняйте аудитируемость без лишних сложностей

Исключения — это место, где возникают споры («Кто сменил мою смену?»). Добавьте базовые поля аудита в таблицы пропусков и переопределений: created_by, created_at, updated_by, updated_at и опционально причину.

Распространённые ошибки, вызывающие баги с «минус/плюс один час»

Большинство часовых багов возникают из-за смешения двух смыслов времени: мгновения (точка на временной шкале UTC) и показания часов (например, 09:00 каждый понедельник в Нью-Йорке).

Классическая ошибка — хранить локальное правило как timestamptz. Если вы сохраняете «понедельники в 09:00 America/New_York» в одном timestamptz, вы уже выбрали конкретную дату (и состояние DST). Позже, при генерации будущих понедельников, первоначальный смысл «всегда 09:00 локально» исчезнет.

Ещё одна частая причина — полагаться на фиксированные UTC-смещения вроде -05:00 вместо IANA-имени. Смещения не включают правила перехода на летнее время. Храните идентификатор зоны (например, America/New_York) и дайте PostgreSQL применить правильные правила для каждой даты.

Будьте осторожны с моментом конвертации. Если вы конвертируете в UTC слишком рано при генерации повторений, вы можете зафиксировать смещение DST и применить его ко всем экземплярам. Безопасный паттерн: генерируйте в локальных терминах (дата + местное время + зона), затем конвертируйте каждый экземпляр в мгновение.

Ошибки, которые повторяются:

• Использование timestamptz для хранения повторяющегося локального времени дня (нужно time + tzid + правило).
• Хранение только смещения, а не IANA-зоны.
• Конвертация во время генерации повторений вместо в конце.
• Разворачивание «всегда» без жёсткого временного окна.
• Отсутствие тестов для недель перехода на DST и обратного перехода.

Простой тест, который ловит большинство проблем: выберите зону с DST, создайте еженедельную смену в 09:00 и отрисуйте двухмесячный календарь, пересекающий смену DST. Убедитесь, что каждый экземпляр отображается как 09:00 локально, даже если соответствующие UTC-моменты отличаются.

Быстрая контрольная проверка перед релизом

Перед выпуском проверьте базовые вещи:

• Каждое расписание привязано к месту (или бизнес-единице) с именованным часовым поясом, сохранённым в самом расписании.
• Вы храните IANA-идентификаторы зон (например, America/New_York), а не чистые смещения.
• Разворачивание повторений происходит только внутри запрошенного диапазона.
• У исключений и переопределений один, задокументированный порядок приоритета.
• Вы тестируете недели перехода на DST и сценарий с просмотром из другого часового пояса.

Сделайте реалистичную репетицию: магазин в Europe/Berlin имеет еженедельную смену в 09:00 по местному времени. Менеджер смотрит её из America/Los_Angeles. Убедитесь, что смена остаётся 09:00 берлинского времени каждую неделю, даже когда регионы переходят на DST в разные даты.

Пример: еженедельные смены с праздником и сменой DST

Небольшая клиника проводит одну повторяющуюся смену: каждый понедельник с 09:00 до 17:00 в местном часовом поясе клиники (America/New_York). Клиника закрыта в один конкретный понедельник (праздник). Сотрудник путешествует две недели по Европе, но расписание клиники должно оставаться привязанным к местному времени клиники, а не к текущему месту сотрудника.

Чтобы всё работало правильно:

• Сохраняйте правило, привязанное к локальным датам (день недели = понедельник, локальные времена = 09:00–17:00).
• Сохраняйте часовой пояс расписания (America/New_York).
• Сохраняйте эффективную дату начала, чтобы правило имело ясную опору.
• Сохраняйте исключение, чтобы отменить праздничный понедельник (и переопределения для одноразовых изменений).

Теперь отрисуйте двухнедельный диапазон, включающий переход DST в Нью-Йорке. Запрос генерирует понедельники в этом локальном диапазоне дат, прикрепляет локальные времена клиники, а затем конвертирует каждый экземпляр в абсолютное мгновение (timestamptz). Поскольку конвертация выполняется для каждого экземпляра, DST обрабатывается корректно в нужный день.

Разные зрители увидят разные локальные показания для одного и того же мгновения:

• Менеджер в Лос-Анджелесе увидит его раньше по своим часам.
• Путешествующий сотрудник в Берлине увидит его позже по своим часам.

Клиника всё ещё получает то, что хотела: 09:00–17:00 по Нью-Йорку каждый понедельник, который не отменён.

Следующие шаги: реализуйте, протестируйте и держите в поддерживаемом виде

Заранее зафиксируйте подход к времени: будете ли вы хранить только правила, только экземпляры или гибрид? Для многих продуктов бронирования и смен гибрид — хорошее решение: правило как источник истины, кеш для скользящего окна при необходимости и явные строки для исключений и переопределений.

Запишите вашу «договорённость о времени» в одном месте: что считается мгновением, что считается локальным временем по часам и какие столбцы за что отвечают. Это предотвратит рассинхрон, когда один endpoint возвращает локальное время, а другой — UTC.

Держите генерацию повторений в одном модуле, а не разбросанной по SQL-фрагментам. Если когда-нибудь вы измените интерпретацию «09:00 местного времени», вам нужно будет обновить одну точку.

Если вы создаёте инструмент планирования, не реализуя всё вручную, AppMaster (appmaster.io) — практичное решение для такой работы: можно смоделировать базу в Data Designer, построить логику повторений и исключений в бизнес-процессах и получить реальный сгенерированный бэкенд и код приложения.