Programaciones recurrentes y zonas horarias en PostgreSQL: patrones

Por qué fallan las zonas horarias y los eventos recurrentes

La mayoría de los errores de calendario no son errores de matemáticas. Son errores de significado. Guardas una cosa (un instante en el tiempo), pero los usuarios esperan otra (una hora de reloj local en un lugar específico). Esa brecha explica por qué las programaciones recurrentes y las zonas horarias pueden verse bien en pruebas y fallar cuando aparecen usuarios reales.

El horario de verano (DST) es el detonante clásico. Un cambio que se define como “cada domingo a las 09:00” no es lo mismo que “cada 7 días desde una marca temporal inicial”. Cuando cambia el desplazamiento, esas dos ideas se separan por una hora y tu calendario se vuelve incorrecto silenciosamente.

Los viajes y las zonas horarias mixtas añaden otra capa. Una reserva puede estar ligada a un lugar físico (una silla en un salón en Chicago), mientras que la persona que la ve está en Londres. Si tratas una programación basada en lugar como si fuera basada en persona, mostrarás la hora local equivocada a al menos uno de los lados.

Modos comunes de fallo:

• Generas recurrencias sumando un intervalo a un timestamp almacenado, y luego cambia el DST.
• Almacenas “horas locales” sin las reglas de zona, así que no puedes reconstruir los instantes intencionados después.
• Solo pruebas fechas que nunca cruzan una frontera de DST.
• Mezclas “zona horaria del evento”, “zona horaria del usuario” y “zona horaria del servidor” en una misma consulta.

Antes de elegir un esquema, decide qué significa “correcto” para tu producto.

Para una reserva, “correcto” suele significar: la cita ocurre a la hora de reloj prevista en la zona horaria del lugar, y todas las personas que la ven obtienen una conversión correcta.

Para un turno, “correcto” a menudo significa: el turno empieza a una hora local fija para la tienda, incluso si un empleado está viajando.

Esa decisión (programación ligada a un lugar vs. a una persona) lo determina todo: qué almacenar, cómo generar recurrencias y cómo consultar una vista de calendario sin sorpresas de una hora.

Elige el modelo mental correcto: instante vs. hora local

Muchos errores vienen de mezclar dos ideas diferentes de tiempo:

• Un instante: un momento absoluto que ocurre una vez.
• Una regla de hora local: una hora de reloj como “todos los lunes a las 9:00 AM en París”.

Un instante es el mismo en todas partes. “2026-03-10 14:00 UTC” es un instante. Llamadas de vídeo, salidas de vuelo y “envía esta notificación exactamente en este momento” suelen ser instantes.

La hora local es lo que la gente lee en un reloj en un lugar. “9:00 AM en Europe/Paris todos los días laborables” es hora local. Horarios de apertura, clases recurrentes y turnos del personal suelen estar anclados a la zona horaria de una ubicación. La zona horaria es parte del significado, no una preferencia de visualización.

Una regla simple de uso:

• Guarda start/end como instantes cuando el evento debe ocurrir en un mismo momento real en todo el mundo.
• Guarda fecha local y hora local más un identificador de zona cuando el evento debe seguir el reloj en un lugar.
• Si los usuarios viajan, muestra horas en la zona del espectador, pero mantiene la programación anclada a su zona.
• No adivines una zona a partir de offsets como "+02:00". Los offsets no incluyen reglas de DST.

Ejemplo: un turno hospitalario es “Lun-Vie 09:00-17:00 America/New_York.” En la semana del cambio de DST, el turno sigue siendo de 9 a 5 localmente, aunque los instantes UTC se muevan una hora.

Tipos de PostgreSQL que importan (y cuáles evitar)

La mayoría de los errores de calendario comienzan con un tipo de columna equivocado. La clave es separar un momento real de una expectativa de reloj local.

Usa timestamptz para instantes reales: reservas, fichajes, notificaciones y cualquier cosa que compares entre usuarios o regiones. PostgreSQL lo guarda como un instante absoluto y lo convierte para mostrar, así que ordenar y comprobar solapamientos se comporta como esperas.

Usa timestamp without time zone para valores de reloj local que no son instantes por sí solos, como “todos los lunes a las 09:00” o “la tienda abre a las 10:00”. Combínalo con un identificador de zona y convierte a instante real solo cuando generes ocurrencias.

Para patrones recurrentes, los tipos básicos ayudan:

• date para excepciones de día completo (festivos)
• time para una hora de inicio diaria
• interval para duraciones (como un turno de 6 horas)

Almacena la zona horaria como un nombre IANA (por ejemplo, America/New_York) en una columna text (o en una tabla de referencia pequeña). Offsets como -0500 no son suficientes porque no contienen reglas de horario de verano.

Un conjunto práctico para muchas apps:

• timestamptz para start/end instantes de citas reservadas
• date para días de excepción
• time para la hora de inicio recurrente local
• interval para la duración
• text para el ID de zona IANA

Opciones de modelo de datos para apps de reservas y turnos

El mejor esquema depende de con qué frecuencia cambian las programaciones y con cuánta antelación la gente las consulta. Normalmente eliges entre escribir muchas filas por adelantado o generarlas cuando alguien abre un calendario.

Opción A: almacenar cada ocurrencia

Insertar una fila por turno o reserva (ya expandida). Es fácil de consultar y de razonar. El intercambio es escrituras pesadas y muchas actualizaciones cuando cambia una regla.

Funciona bien cuando los eventos son mayormente puntuales, o cuando solo creas ocurrencias a corto plazo (por ejemplo, los próximos 30 días).

Opción B: almacenar una regla y expandir en lectura

Almacena una regla de schedule (como “semanal los lun y mié a 09:00 en America/New_York”) y genera ocurrencias para el rango solicitado bajo demanda.

Es flexible y ligero en almacenamiento, pero las consultas se vuelven más complejas. Las vistas mensuales también pueden volverse lentas a menos que caches resultados.

Opción C: regla más ocurrencias cacheadas (híbrido)

Mantén la regla como fuente de verdad y también guarda ocurrencias generadas para una ventana móvil (por ejemplo, 60–90 días). Cuando la regla cambia, regenera la caché.

Esto es un buen valor por defecto para apps de turnos: las vistas mensuales siguen siendo rápidas, pero tienes un único lugar para editar el patrón.

Un conjunto práctico de tablas:

• schedule: owner/resource, zona horaria, hora de inicio local, duración, regla de recurrencia
• occurrence: instancias expandidas con start_at timestamptz, end_at timestamptz, más estado
• exception: marcadores de “saltar esta fecha” o “esta fecha es diferente”
• override: ediciones por ocurrencia como cambio de hora de inicio, intercambio de personal, bandera de cancelado
• (opcional) schedule_cache_state: última ventana generada para saber qué rellenar a continuación

Para consultas por rango de calendario, indexa para “muéstrame todo en esta ventana”:

• En occurrence: btree (resource_id, start_at) y a menudo btree (resource_id, end_at)
• Si consultas “se solapa con un rango” a menudo: una tstzrange(start_at, end_at) generada más un índice gist

Representar reglas de recurrencia sin hacerlas frágiles

Los horarios recurrentes se rompen cuando la regla es demasiado ingeniosa, demasiado flexible o se almacena como un blob no consultable. Un buen formato de regla es uno que tu app pueda validar y que tu equipo pueda explicar rápidamente.

Dos enfoques comunes:

• Campos simples personalizados para los patrones que realmente soportas (turnos semanales, fechas de facturación mensuales).
• Reglas estilo iCalendar (RRULE) cuando necesitas importar/exportar calendarios o soportar muchas combinaciones.

Un punto intermedio práctico: permite un conjunto limitado de opciones, almacénalas en columnas y trata cualquier cadena RRULE como solo intercambio.

Por ejemplo, una regla semanal de turnos puede expresarse con campos como:

• freq (daily/weekly/monthly) e interval (cada N)
• byweekday (un array de 0-6 o una máscara de bits)
• bymonthday opcional (1-31) para reglas mensuales
• starts_at_local (la fecha+hora local que eligió el usuario) y tzid
• until_date u count opcionales (evita soportar ambos a menos que realmente lo necesites)

Para límites, prefiere almacenar duración (por ejemplo, 8 horas) en vez de almacenar un end timestamp para cada ocurrencia. La duración permanece estable cuando cambian los relojes. Aun así puedes calcular un end por ocurrencia como: inicio de la ocurrencia + duración.

Al expandir una regla, mantenla segura y acotada:

• Expande solo dentro de window_start y window_end.
• Añade un pequeño buffer (por ejemplo, 1 día) para eventos que duren la noche.
• Para al final después de un número máximo de instancias (como 500).
• Filtra candidatos primero (por tzid, freq y fecha de inicio) antes de generar.

Paso a paso: construir una programación recurrente segura frente al DST

Un patrón fiable es: trata cada ocurrencia primero como una idea de calendario local (fecha + hora local + zona del lugar), luego conviértela a un instante solo cuando necesites ordenar, comprobar conflictos o mostrarla.

1) Almacena la intención local, no suposiciones UTC

Guarda la zona horaria de la programación (nombre IANA como America/New_York) más una hora de inicio local (por ejemplo 09:00). Esa hora local es lo que el negocio quiere, incluso cuando cambia el DST.

También guarda una duración y límites claros para la regla: una fecha de inicio y o bien una fecha de fin o un conteo de repeticiones. Los límites evitan errores de “expansión infinita”.

2) Modela excepciones y overrides por separado

Usa dos tablas pequeñas: una para fechas omitidas y otra para ocurrencias modificadas. Llámalas por schedule_id + local_date para poder emparejar la recurrencia original limpiamente.

Una forma práctica:

-- 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) Expande solo dentro de la ventana solicitada

Genera fechas locales candidatas para el rango que estás renderizando (semana, mes). Filtra por día de la semana y después aplica skips y overrides.

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) Convierte para el espectador al final

Mantén start_utc como timestamptz para ordenar, chequear conflictos y reservas. Solo cuando muestres, convierte a la zona horaria del espectador. Esto evita sorpresas de DST y mantiene las vistas de calendario coherentes.

Patrones de consulta para generar una vista de calendario correcta

Una pantalla de calendario suele ser una consulta por rango: “muéstrame todo entre from_ts y to_ts.” Un patrón seguro es:

1. Expande solo candidatos en esa ventana.
2. Aplica excepciones/overrides.
3. Devuelve filas finales con start_at y end_at como timestamptz.

Expansión diaria o semanal con generate_series

Para reglas semanales simples (como “cada Lun-Vie a 09:00 local”), genera fechas locales en la zona de la programación y luego convierte cada fecha local + hora local en un instante.

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

Esto funciona bien porque la conversión a timestamptz ocurre por ocurrencia, de modo que los cambios de DST se aplican en el día correcto.

Reglas más complejas con un CTE recursivo

Cuando las reglas dependen de “n-ésimo día de la semana”, huecos o intervalos personalizados, un CTE recursivo puede generar la siguiente ocurrencia repetidamente hasta que supere to_ts. Mantén la recursión anclada a la ventana para que no pueda ejecutarse indefinidamente.

Tras obtener filas candidatas, aplica overrides y cancelaciones uniendo las tablas de excepción sobre (rule_id, start_at) o sobre una clave local como (rule_id, local_date). Si hay un registro de cancelación, elimina la fila. Si hay un override, reemplaza start_at/end_at con los valores del override.

Patrones de rendimiento que importan:

• Restringe el rango temprano: filtra las reglas primero y luego expande solo dentro de [from_ts, to_ts).
• Indexa tablas de excepción/override en (rule_id, start_at) o (rule_id, local_date).
• Evita expandir años de datos para una vista mensual.
• Cachea ocurrencias expandidas solo si puedes invalidarlas limpiamente cuando cambian las reglas.

Manejar excepciones y overrides de forma limpia

Los horarios recurrentes solo son útiles si puedes romperlos con seguridad. En apps de reservas y turnos, la “semana normal” es la regla base, y todo lo demás es una excepción: festivos, cancelaciones, citas movidas o intercambios de personal. Si las excepciones se añaden después, las vistas de calendario se desvían y aparecen duplicados.

Mantén tres conceptos separados:

• Un schedule base (la regla recurrente y su zona horaria)
• Skips (fechas o instancias que no deben ocurrir)
• Overrides (una ocurrencia que existe pero con detalles cambiados)

Usa un orden de precedencia fijo

Elige un orden y mantenlo consistente. Una elección común:

1. Genera candidatos desde la recurrencia base.
2. Aplica overrides (reemplaza la generada).
3. Aplica skips (ocúltala).

Asegúrate de que la regla se explique fácilmente a los usuarios en una sola frase.

Evita duplicados cuando un override reemplaza una instancia

Los duplicados suelen ocurrir cuando una consulta devuelve tanto la ocurrencia generada como la fila de override. Evítalo con una clave estable:

• Da a cada instancia generada una clave estable, como (schedule_id, local_date, start_time, tzid).
• Almacena esa clave en la fila de override como la “clave de ocurrencia original”.
• Añade una restricción única para que solo exista un override por ocurrencia base.

Entonces, en las consultas, excluye ocurrencias generadas que tengan un override correspondiente y une las filas de override.

Mantén la auditabilidad sin fricción

Las excepciones son donde surgen disputas (“¿Quién cambió mi turno?”). Añade campos básicos de auditoría en skips y overrides: created_by, created_at, updated_by, updated_at y una razón opcional.

Errores comunes que causan bugs de ±1 hora

La mayoría de los errores de una hora vienen de mezclar dos significados de tiempo: un instante (un punto en la línea de tiempo UTC) y una lectura del reloj local (como 09:00 todos los lunes en Nueva York).

Un error clásico es almacenar una regla de reloj local como timestamptz. Si guardas “Lunes a las 09:00 America/New_York” como un único timestamptz, ya has elegido una fecha específica (y un estado de DST). Más tarde, cuando generes futuros lunes, la intención original (“siempre 09:00 local”) se habrá perdido.

Otra causa frecuente es confiar en offsets UTC fijos como -05:00 en vez de un nombre de zona IANA. Los offsets no incluyen reglas de DST. Guarda el ID de zona (por ejemplo, America/New_York) y deja que PostgreSQL aplique las reglas correctas para cada fecha.

Ten cuidado con el momento de la conversión. Si conviertes a UTC demasiado pronto al generar una recurrencia, puedes fijar un offset de DST y aplicarlo a todas las ocurrencias. Un patrón más seguro es: genera ocurrencias en términos locales (fecha + hora local + zona) y luego convierte cada ocurrencia a un instante.

Errores que se repiten:

• Usar timestamptz para almacenar una hora de día recurrente local (necesitabas time + tzid + una regla).
• Almacenar solo un offset, no el nombre IANA.
• Convertir durante la generación de recurrencias en lugar de hacerlo al final.
• Expandir recurrencias “para siempre” sin una ventana temporal rígida.
• No probar la semana de inicio y la de fin de DST.

Una prueba simple que atrapa la mayoría de los problemas: elige una zona con DST, crea un turno semanal a las 09:00 y renderiza un calendario de dos meses que cruce un cambio de DST. Verifica que cada instancia se muestre como 09:00 local, aunque los instantes UTC subyacentes difieran.

Checklist rápido antes de lanzar

Antes del lanzamiento, verifica lo básico:

• Cada schedule está vinculado a un lugar (o unidad de negocio) con una zona horaria nombrada, almacenada en la propia programación.
• Guardas IDs de zona IANA (como America/New_York), no offsets brutos.
• La expansión de recurrencia genera ocurrencias solo dentro del rango solicitado.
• Excepciones y overrides tienen un único orden de precedencia documentado.
• Pruebas las semanas de cambio de DST y un espectador en una zona horaria distinta a la del schedule.

Haz una simulación realista: una tienda en Europe/Berlin tiene un turno semanal a las 09:00 hora local. Un gerente lo ve desde America/Los_Angeles. Confirma que el turno se mantiene 09:00 hora de Berlín cada semana, incluso cuando cada región cruza DST en fechas diferentes.

Ejemplo: turnos semanales con un festivo y un cambio de DST

Una pequeña clínica tiene un turno recurrente: todos los lunes, de 09:00 a 17:00 en la zona local de la clínica (America/New_York). La clínica cierra por un festivo en un lunes concreto. Un empleado está de viaje en Europa dos semanas, pero el horario de la clínica debe permanecer ligado al reloj de la clínica, no a la ubicación actual del empleado.

Para que esto se comporte correctamente:

• Almacena una regla de recurrencia anclada a fechas locales (weekday = Monday, horas locales = 09:00 a 17:00).
• Guarda la zona horaria del schedule (America/New_York).
• Almacena una fecha de inicio efectiva para que la regla tenga un ancla clara.
• Almacena una excepción para cancelar el lunes festivo (y overrides para cambios puntuales).

Ahora renderiza un rango de calendario de dos semanas que incluya un cambio de DST en Nueva York. La consulta genera los lunes en ese rango de fechas locales, adjunta las horas locales de la clínica y luego convierte cada ocurrencia a un instante absoluto (timestamptz). Como la conversión se hace por ocurrencia, el DST se maneja en el día correcto.

Diferentes espectadores verán distintas horas de reloj para el mismo instante:

• Un gerente en Los Ángeles lo verá antes en el reloj.
• Un miembro del personal viajando en Berlín lo verá más tarde en el reloj.

La clínica aún obtiene lo que quería: 09:00 a 17:00 hora de Nueva York, todos los lunes que no estén cancelados.

Siguientes pasos: implementar, probar y mantener

Fija tu enfoque sobre el tiempo pronto: ¿almacenarás solo reglas, solo ocurrencias o un híbrido? Para muchos productos de reservas y turnos, un híbrido funciona bien: conserva la regla como fuente de verdad, guarda una caché móvil si hace falta y guarda excepciones y overrides como filas concretas.

Escribe tu “contrato de tiempo” en un solo lugar: qué cuenta como instante, qué cuenta como hora de reloj local y qué columnas almacenan cada cosa. Esto evita desalineaciones donde un endpoint devuelve hora local mientras otro devuelve UTC.

Mantén la generación de recurrencias en un solo módulo, no distribuida en fragmentos SQL por toda la base de código. Si alguna vez cambias cómo interpretas “09:00 AM hora local”, querrás un único sitio para actualizar.

Si construyes una herramienta de programación sin codificarlo todo a mano, AppMaster (appmaster.io) es una opción práctica para este tipo de trabajo: puedes modelar la base de datos en su Data Designer, construir la lógica de recurrencia y excepciones en procesos de negocio visuales y aun así obtener código backend y de app real generado.