Horaires récurrents et fuseaux horaires dans PostgreSQL : bonnes pratiques

Pourquoi les fuseaux horaires et les événements récurrents posent problème

La plupart des bugs de calendrier ne sont pas des erreurs de calcul. Ce sont des erreurs de sens. Vous stockez une chose (un instant précis), mais les utilisateurs en attendent une autre (une heure affichée localement dans un lieu donné). Cet écart explique pourquoi les horaires récurrents et les fuseaux horaires passent les tests puis cassent dès que de vrais utilisateurs arrivent.

Le changement d'heure (DST) est le déclencheur classique. Un décalage "tous les dimanches à 09:00" n'est pas la même chose que "tous les 7 jours à partir d'un timestamp de départ". Quand l'offset change, ces deux idées dérivent d'une heure et votre calendrier devient silencieusement incorrect.

Les déplacements et les fuseaux mixtes ajoutent une couche supplémentaire. Une réservation peut être liée à un lieu physique (un fauteuil dans un salon à Chicago), tandis que la personne qui la regarde est à Londres. Si vous traitez un planning lié au lieu comme s'il était lié à la personne, vous afficherez une mauvaise heure locale à au moins une des parties.

Modes d'échec courants :

• Vous générez des récurrences en ajoutant un intervalle à un timestamp stocké, puis le DST change.
• Vous stockez des "heures locales" sans les règles de fuseau, donc vous ne pouvez pas reconstruire les instants voulus plus tard.
• Vous testez seulement des dates qui ne croisent jamais une frontière DST.
• Vous mélangez "fuseau de l'événement", "fuseau de l'utilisateur" et "fuseau du serveur" dans une même requête.

Avant de choisir un schéma, décidez ce que signifie « correct » pour votre produit.

Pour une réservation, "correct" signifie généralement : le rendez-vous a lieu à l'heure affichée sur l'horloge locale du lieu, et toute personne qui le consulte obtient une conversion correcte.

Pour un shift, "correct" signifie souvent : le shift commence à une heure locale fixe pour le magasin, même si un employé est en voyage.

Cette décision (planning lié au lieu vs à la personne) oriente tout le reste : ce que vous stockez, comment vous générez les récurrences, et comment vous interrogez une vue calendrier sans surprises d'une heure.

Choisir le bon modèle mental : instant vs heure locale

Beaucoup de bugs viennent du mélange de deux idées différentes du temps :

• Un instant : un moment absolu qui se produit une fois.
• Une règle d'heure locale : une heure sur l'horloge, comme « chaque lundi à 9:00 à Paris ».

Un instant est identique partout. "2026-03-10 14:00 UTC" est un instant. Les appels vidéo, les départs de vol et "envoyer cette notification à ce moment précis" sont généralement des instants.

L'heure locale est ce que les gens lisent sur une horloge dans un lieu. "9:00 à Europe/Paris chaque jour ouvrable" est une heure locale. Les horaires d'ouverture, les cours récurrents et les shifts du personnel sont généralement ancrés au fuseau d'un lieu. Le fuseau fait partie du sens, ce n'est pas une préférence d'affichage.

Une règle simple :

• Stockez start/end comme des instants (timestamptz) quand l'événement doit avoir lieu à un moment précis dans le monde.
• Stockez la date locale et l'heure locale plus un identifiant de zone quand l'événement doit suivre l'horloge d'un lieu.
• Si les utilisateurs voyagent, affichez les heures dans le fuseau du spectateur, mais gardez le planning ancré à son fuseau.
• Ne devinez pas un fuseau à partir d'offsets comme "+02:00". Les offsets n'incluent pas les règles de DST.

Exemple : un shift d'hôpital est « Lun-Ven 09:00-17:00 America/New_York ». La semaine du changement d'heure, le shift reste 9 à 17 localement, même si les instants UTC bougent d'une heure.

Types PostgreSQL qui comptent (et à éviter)

La plupart des bugs de calendrier commencent par un mauvais type de colonne. L'important est de séparer un instant réel d'une attente liée à l'horloge.

Utilisez timestamptz pour les instants réels : réservations, pointages, notifications, et tout ce que vous comparez entre utilisateurs ou régions. PostgreSQL le stocke comme un instant absolu et le convertit pour l'affichage, donc le tri et les vérifications de chevauchement se comportent comme prévu.

Utilisez timestamp without time zone pour des valeurs d'horloge locale qui ne sont pas des instants en elles-mêmes, comme « chaque lundi à 09:00 » ou « le magasin ouvre à 10:00 ». Associez-le à un identifiant de fuseau, puis convertissez en instant réel seulement lors de la génération des occurrences.

Pour les motifs récurrents, les types de base aident :

• date pour les exceptions portant seulement sur le jour (jours fériés)
• time pour une heure de début quotidienne
• interval pour les durées (par exemple un shift de 6 heures)

Stockez le fuseau comme un nom IANA (par exemple America/New_York) dans une colonne text (ou une petite table de référence). Les offsets comme -0500 ne suffisent pas car ils n'incluent pas les règles d'heure d'été.

Un ensemble pratique pour beaucoup d'apps :

• timestamptz pour les start/end instants des rendez-vous réservés
• date pour les jours d'exception
• time pour l'heure locale de début récurrente
• interval pour la durée
• text pour l'ID de fuseau IANA

Options de modèle de données pour applications de réservation et de shifts

Le meilleur schéma dépend de la fréquence des changements de planning et de la profondeur de navigation des utilisateurs. Vous choisissez souvent entre écrire beaucoup de lignes à l'avance ou les générer à la lecture.

Option A : stocker chaque occurrence

Insérez une ligne par shift ou réservation (déjà développée). C'est simple à interroger et facile à raisonner. L'échange est des écritures lourdes et beaucoup de mises à jour quand une règle change.

Ceci fonctionne bien quand les événements sont majoritairement ponctuels, ou quand vous ne créez les occurrences que pour une courte fenêtre (par exemple les 30 prochains jours).

Option B : stocker une règle et développer à la lecture

Stockez une règle de planning (comme « hebdomadaire le lun et mer à 09:00 en America/New_York ») et générez les occurrences pour la plage demandée à la volée.

C'est flexible et léger en stockage, mais les requêtes deviennent plus complexes. Les vues mensuelles peuvent aussi ralentir à moins de mettre en cache les résultats.

Option C : règle + occurrences mises en cache (hybride)

Conservez la règle comme source de vérité, et stockez aussi les occurrences générées pour une fenêtre roulante (par exemple 60–90 jours). Quand la règle change, régénérez le cache.

C'est un bon compromis par défaut pour les apps de shifts : les vues mensuelles restent rapides, mais vous avez toujours un lieu unique pour éditer le motif.

Un jeu de tables pratique :

• schedule : owner/resource, time zone, heure locale de début, durée, règle de récurrence
• occurrence : instances développées avec start_at timestamptz, end_at timestamptz, plus le statut
• exception : marqueurs « sauter cette date » ou « cette date est différente »
• override : modifications par occurrence comme changement d'heure de début, échange de personnel, drapeau annulé
• (optionnel) schedule_cache_state : dernière plage générée pour savoir quoi remplir ensuite

Pour les requêtes sur plage calendrier, indexez pour « montrez-moi tout dans cette fenêtre » :

• Sur occurrence : btree (resource_id, start_at) et souvent btree (resource_id, end_at)
• Si vous interrogez souvent « chevauche la plage » : un tstzrange(start_at, end_at) généré avec un index gist

Représenter les règles de récurrence sans les rendre fragiles

Les plannings récurrents cassent quand la règle est trop astucieuse, trop flexible ou stockée comme un blob impossible à interroger. Un bon format de règle est celui que votre appli peut valider et que votre équipe peut expliquer rapidement.

Deux approches courantes :

• Champs simples personnalisés pour les motifs que vous supportez réellement (shifts hebdomadaires, dates de facturation mensuelles).
• Règles de type iCalendar (style RRULE) lorsque vous devez importer/exporter des calendriers ou supporter beaucoup de combinaisons.

Un compromis pratique : autoriser un ensemble limité d'options, les stocker en colonnes, et traiter toute chaîne RRULE comme interopérable seulement.

Par exemple, une règle hebdomadaire peut s'exprimer avec des champs comme :

• freq (daily/weekly/monthly) et interval (tous les N)
• byweekday (un tableau de 0–6 ou un bitmask)
• bymonthday optionnel (1–31) pour les règles mensuelles
• starts_at_local (la date+heure locale choisie) et tzid
• until_date optionnel ou count (évitez de supporter les deux sauf si nécessaire)

Pour les limites, préférez stocker la durée (par exemple 8 heures) plutôt que l'end timestamp pour chaque occurrence. La durée reste stable lors des changements d'heure. Vous pouvez toujours calculer un end par occurrence : start + duration.

Lors de l'expansion d'une règle, limitez-la et sécurisez-la :

• N'expandez que dans window_start et window_end.
• Ajoutez un petit buffer (par exemple 1 jour) pour les événements qui traversent la nuit.
• Arrêtez après un nombre maximum d'instances (par exemple 500).
• Filtrez d'abord les candidats (par tzid, freq et date de début) avant de générer.

Pas à pas : construire un planning récurrent sûr pour le DST

Un modèle fiable est : traitez chaque occurrence d'abord comme une idée de calendrier locale (date + heure locale + fuseau du lieu), puis convertissez en instant uniquement quand vous devez trier, vérifier les conflits ou afficher.

1) Stocker l'intention locale, pas des suppositions UTC

Enregistrez le fuseau du planning (nom IANA comme America/New_York) plus une heure locale de début (par exemple 09:00). Cette heure locale est ce que l'entreprise veut, même quand le DST bouge.

Stockez aussi une durée et des bornes claires pour la règle : une date de début, et soit une date de fin soit un nombre de répétitions. Les bornes évitent les bugs d'"expansion infinie".

2) Modéliser séparément exceptions et overrides

Utilisez deux petites tables : une pour les dates sautées, une pour les occurrences modifiées. Cleflez-les par schedule_id + local_date afin de pouvoir faire correspondre la récurrence d'origine proprement.

Une forme pratique ressemble à ceci :

-- 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) N'expander que dans la fenêtre demandée

Générez des dates locales candidates pour la plage que vous affichez (semaine, mois). Filtrez par jour de la semaine, puis appliquez skips et 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) Convertir pour le spectateur en dernier

Conservez start_utc comme timestamptz pour le tri, les vérifications de conflit et les réservations. Ce n'est que lors de l'affichage que vous convertissez dans le fuseau du spectateur. Cela évite les surprises DST et garde les vues calendrier cohérentes.

Schémas de requêtes pour générer une vue calendrier correcte

Un écran calendrier est généralement une requête sur une plage : « montrez-moi tout entre from_ts et to_ts ». Un schéma sûr est :

1. N'expandez que les candidats dans cette fenêtre.
2. Appliquez exceptions/overrides.
3. Retournez les lignes finales avec start_at et end_at en timestamptz.

Expansion quotidienne ou hebdomadaire avec generate_series

Pour des règles hebdomadaires simples (comme "chaque Lun–Ven à 09:00 local"), générez des dates locales dans le fuseau du planning, puis transformez chaque date locale + heure locale en un 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);

Cela fonctionne bien car la conversion en timestamptz se fait par occurrence, donc les changements DST sont appliqués le bon jour.

Règles plus complexes avec un CTE récursif

Quand les règles dépendent du "nième jour de semaine", d'écarts ou d'intervalles personnalisés, un CTE récursif peut générer l'occurrence suivante jusqu'à ce qu'elle dépasse to_ts. Gardez la récursion ancrée à la fenêtre pour qu'elle ne tourne pas indéfiniment.

Après avoir obtenu les lignes candidates, appliquez overrides et annulations en joignant les tables d'exception sur (rule_id, start_at) ou sur une clé locale comme (rule_id, local_date). S'il y a un enregistrement d'annulation, supprimez la ligne. S'il y a un override, remplacez start_at/end_at par les valeurs d'override.

Les patterns de performance qui comptent le plus :

• Contraignez la plage tôt : filtrez d'abord les règles, puis n'expandez que dans [from_ts, to_ts).
• Indexez les tables d'exception/override sur (rule_id, start_at) ou (rule_id, local_date).
• Évitez d'expanser des années de données pour une vue mensuelle.
• Mettez en cache les occurrences développées seulement si vous pouvez les invalider proprement quand les règles changent.

Gérer proprement exceptions et overrides

Les plannings récurrents ne servent que si vous pouvez les interrompre en toute sécurité. Dans les apps de réservation et de shifts, la "semaine normale" est la règle de base, et tout le reste est une exception : jours fériés, annulations, rendez-vous déplacés ou échanges de personnel. Si les exceptions sont ajoutées a posteriori, les vues calendrier dérivent et des doublons apparaissent.

Séparez clairement trois concepts :

• Un planning de base (la règle récurrente et son fuseau)
• Les skips (dates ou instances qui ne doivent pas avoir lieu)
• Les overrides (une occurrence qui existe mais avec des détails modifiés)

Utiliser un ordre de priorité fixe

Choisissez un ordre et conservez-le. Un choix courant :

1. Générez les candidats depuis la récurrence de base.
2. Appliquez les overrides (remplacez l'occurrence générée).
3. Appliquez les skips (la masquer).

Assurez-vous que la règle est simple à expliquer aux utilisateurs en une phrase.

Éviter les doublons quand un override remplace une instance

Les doublons surviennent quand une requête retourne à la fois l'occurrence générée et la ligne d'override. Prévenez cela avec une clé stable :

• Donnez à chaque instance générée une clé stable, par exemple (schedule_id, local_date, start_time, tzid).
• Stockez cette clé sur la ligne d'override comme "original occurrence key".
• Ajoutez une contrainte unique pour qu'il n'existe qu'un seul override par occurrence de base.

Puis, dans les requêtes, excluez les occurrences générées qui ont un override correspondant et unionnez les lignes d'override.

Conserver l'auditabilité sans friction

Les exceptions sont souvent sources de litiges ("Qui a changé mon shift ?"). Ajoutez des champs d'audit basiques sur les skips et overrides : created_by, created_at, updated_by, updated_at, et un motif optionnel.

Erreurs fréquentes qui causent des bugs d'une heure

La plupart des bugs d'une heure viennent du mélange de deux sens du temps : un instant (point sur la timeline UTC) et une lecture d'horloge locale (comme 09:00 chaque lundi à New York).

Une erreur classique est de stocker une règle d'heure locale dans timestamptz. Si vous enregistrez "lundis à 09:00 America/New_York" comme un seul timestamptz, vous avez déjà choisi une date spécifique (et un état de DST). Plus tard, quand vous générez des lundis futurs, l'intention d'origine ("toujours 09:00 local") est perdue.

Une autre cause fréquente est de se fier à des offsets UTC fixes comme -05:00 au lieu d'un nom de zone IANA. Les offsets n'incluent pas les règles DST. Stockez l'ID de zone (par exemple America/New_York) et laissez PostgreSQL appliquer les règles correctes pour chaque date.

Faites attention au moment de la conversion. Si vous convertissez en UTC trop tôt lors de la génération d'une récurrence, vous pouvez figer un offset DST et l'appliquer à toutes les occurrences. Un schéma plus sûr : générez les occurrences en termes locaux (date + heure locale + zone), puis convertissez chaque occurrence en instant.

Erreurs qui se répètent :

• Utiliser timestamptz pour stocker une heure locale récurrente (il fallait time + tzid + une règle).
• Ne stocker qu'un offset, pas le nom IANA.
• Convertir pendant la génération de récurrence au lieu de la conversion finale.
• Expanser des récurrences « pour toujours » sans fenêtre temporelle stricte.
• Ne pas tester la semaine de début et la semaine de fin du DST.

Un test simple qui attrape la plupart des problèmes : choisissez un fuseau avec DST, créez un shift hebdomadaire à 09:00, et affichez un calendrier de deux mois qui traverse un changement DST. Vérifiez que chaque instance s'affiche à 09:00 local, même si les instants UTC sous-jacents diffèrent.

Checklist rapide avant mise en production

Avant la sortie, vérifiez les bases :

• Chaque planning est lié à un lieu (ou une unité) avec un fuseau nommé, stocké sur le planning lui-même.
• Vous stockez des IDs de zone IANA (comme America/New_York), pas des offsets bruts.
• L'expansion des récurrences génère des occurrences uniquement dans la plage demandée.
• Exceptions et overrides ont un ordre de priorité unique et documenté.
• Vous testez les semaines de changement DST et un spectateur dans un fuseau différent du planning.

Faites un essai réaliste : un magasin en Europe/Berlin a un shift hebdomadaire à 09:00 heure locale. Un manager le consulte depuis America/Los_Angeles. Confirmez que le shift reste à 09:00 Berlin chaque semaine, même lorsque les régions effectuent le DST à des dates différentes.

Exemple : shifts hebdomadaires avec un jour férié et changement DST

Une petite clinique a un shift récurrent : chaque lundi, 09:00–17:00 dans le fuseau local (America/New_York). La clinique ferme pour un jour férié un lundi donné. Un membre du personnel voyage en Europe pendant deux semaines, mais le planning doit rester attaché à l'heure locale de la clinique, pas à la position actuelle de l'employé.

Pour que cela fonctionne correctement :

• Stockez une règle de récurrence ancrée aux dates locales (weekday = Monday, heures locales = 09:00–17:00).
• Stockez le fuseau du planning (America/New_York).
• Stockez une date de début effective pour que la règle ait un point d'ancrage clair.
• Stockez une exception pour annuler le lundi férié (et des overrides pour les changements ponctuels).

Ensuite, affichez une fenêtre de deux semaines incluant un changement DST à New York. La requête génère les lundis dans cette plage de dates locales, attache les heures locales de la clinique, puis convertit chaque occurrence en un instant absolu (timestamptz). Comme la conversion se fait par occurrence, le DST est appliqué au bon jour.

Différents spectateurs verront des heures locales différentes pour le même instant :

• Un manager à Los Angeles le verra plus tôt sur l'horloge.
• Un employé en voyage à Berlin le verra plus tard sur l'horloge.

La clinique obtient toujours ce qu'elle voulait : 09:00–17:00 heure de New York, chaque lundi non annulé.

Étapes suivantes : implémenter, tester et garder la maintenabilité

Verrouillez votre approche du temps tôt : allez-vous stocker seulement la règle, seulement les occurrences, ou un hybride ? Pour beaucoup de produits de réservation et de shifts, un hybride fonctionne bien : gardez la règle comme source de vérité, stockez un cache roulant si nécessaire, et enregistrez exceptions et overrides comme lignes concrètes.

Écrivez votre "contrat temporel" à un endroit unique : ce qui compte comme instant, ce qui compte comme heure locale, et quelles colonnes stockent chaque chose. Cela évite la dérive où un endpoint renvoie l'heure locale tandis qu'un autre renvoie l'UTC.

Conservez la génération des récurrences dans un seul module, pas dispersée en fragments SQL. Si vous changez un jour la façon d'interpréter "9:00 AM heure locale", vous voudrez n'avoir qu'un seul endroit à mettre à jour.

Si vous construisez un outil de planification sans tout coder à la main, AppMaster (appmaster.io) est adapté à ce type de travail : vous pouvez modéliser la base de données dans son Data Designer, construire la logique de récurrence et d'exception dans des processus métier visuels, et obtenir quand même du code backend et applicatif réel généré.