TIMESTAMPTZ vs TIMESTAMP : tableaux de bord et API PostgreSQL

Le vrai problème : un événement, plusieurs interprétations

Un événement se produit une fois, mais il est rapporté d'une douzaine de façons différentes. La base de données stocke une valeur, une API la sérialise, un tableau de bord l'agrège, et chaque personne la voit dans sa propre zone horaire. Si une couche prend une hypothèse différente, la même ligne peut sembler représenter deux instants différents.

C'est pourquoi le choix entre TIMESTAMPTZ et TIMESTAMP n'est pas qu'une préférence de type. Il détermine si une valeur stockée représente un instant précis dans le temps, ou une heure murale qui n'a de sens que pour un lieu donné.

Voici ce qui casse généralement en premier : un tableau de ventes montre des totaux journaliers différents à New York et à Berlin. Un graphique horaire a une heure manquante ou doublée pendant les changements d'heure d'été (DST). Un journal d'audit semble hors ordre parce que deux systèmes « s'accordent » sur la date mais pas sur l'instant réel.

Un modèle simple vous évite des ennuis :

• Stockage : ce que vous enregistrez dans PostgreSQL et ce que cela représente.
• Affichage : comment vous le formatez dans une UI, une exportation ou un rapport.
• Locale utilisateur : la zone horaire et les règles calendaires du visionneur, y compris le DST.

Mélangez ces éléments et vous obtenez des bugs de reporting silencieux. Une équipe support exporte « les tickets créés hier » depuis un tableau de bord, puis le compare à un rapport d'API. Les deux semblent raisonnables, mais l'un a utilisé la frontière minuit locale du visionneur tandis que l'autre a utilisé UTC.

L'objectif est simple : pour chaque valeur temporelle, prenez deux décisions claires. Décidez de ce que vous stockez, et décidez de ce que vous affichez. Cette même clarté doit traverser votre modèle de données, les réponses d'API et les dashboards afin que tout le monde voie la même chronologie.

Ce que TIMESTAMP et TIMESTAMPTZ signifient réellement

Dans PostgreSQL, les noms sont trompeurs. Ils paraissent décrire ce qui est stocké, mais ils décrivent surtout comment PostgreSQL interprète l'entrée et formate la sortie.

TIMESTAMP (ou timestamp without time zone) est juste une date et une heure sur le calendrier, comme 2026-01-29 09:00:00. Aucune zone n'est attachée. PostgreSQL ne la convertira pas pour vous. Deux personnes dans des zones différentes peuvent lire le même TIMESTAMP et supposer des instants réels différents.

TIMESTAMPTZ (ou timestamp with time zone) représente un vrai point dans le temps. Pensez-y comme un instant. PostgreSQL le normalise en interne (effectivement en UTC), puis l'affiche selon la zone de session utilisée.

Le comportement derrière la plupart des surprises est :

• À l'entrée : PostgreSQL convertit les valeurs TIMESTAMPTZ en un instant comparable unique.
• À la sortie : PostgreSQL formate cet instant en utilisant la zone de session courante.
• Pour TIMESTAMP : aucune conversion automatique n'a lieu à l'entrée ou à la sortie.

Un petit exemple montre la différence. Supposons que votre app reçoive 2026-03-08 02:30 d'un utilisateur. Si vous l'insérez dans une colonne TIMESTAMP, PostgreSQL stocke exactement cette valeur murale. Si cette heure locale n'existe pas à cause d'un saut DST, vous ne le remarquerez peut-être que lorsque le reporting casse.

Si vous insérez dans TIMESTAMPTZ, PostgreSQL a besoin d'une zone horaire pour interpréter la valeur. Si vous fournissez 2026-03-08 02:30 America/New_York, PostgreSQL la convertit en un instant (ou renvoie une erreur selon les règles et la valeur exacte). Plus tard, un dashboard à Londres affichera une heure locale différente, mais c'est le même instant.

Une idée fausse courante : les gens voient « with time zone » et s'attendent à ce que PostgreSQL stocke l'étiquette de zone horaire d'origine. Ce n'est pas le cas. PostgreSQL stocke le moment, pas l'étiquette. Si vous avez besoin de la zone d'origine de l'utilisateur pour l'affichage (par exemple, « montrer en heure locale du client »), stockez la zone séparément en tant que champ texte.

Zone de session : le réglage caché derrière bien des surprises

PostgreSQL a un réglage qui change discrètement ce que vous voyez : la zone horaire de session. Deux personnes peuvent exécuter la même requête sur les mêmes données et obtenir des heures différentes parce que leurs sessions utilisent des zones différentes.

Ceci affecte surtout TIMESTAMPTZ. PostgreSQL stocke un instant absolu, puis l'affiche dans la zone de session. Avec TIMESTAMP (sans zone), PostgreSQL traite la valeur comme une simple heure calendaires. Il ne la décale pas pour l'affichage, mais la zone de session peut encore vous nuire lorsque vous la convertissez en TIMESTAMPTZ ou la comparez à des valeurs conscientes du fuseau.

Les zones de session sont souvent définies sans que vous vous en rendiez compte : configuration au démarrage de l'application, paramètres du driver, pools de connexions réutilisant d'anciennes sessions, outils BI avec leurs défauts, jobs ETL héritant des paramètres locaux du serveur, ou consoles SQL manuelles utilisant les préférences de votre laptop.

Voici comment les équipes se disputent. Supposons qu'un événement soit stocké comme 2026-03-08 01:30:00+00 dans une colonne TIMESTAMPTZ. Une session de dashboard en America/Los_Angeles l'affichera comme l'heure locale de la veille au soir, tandis qu'une session API en UTC affichera une heure différente. Si un graphique groupe par jour en utilisant le jour local de la session, vous pouvez obtenir des totaux journaliers divergents.

-- Make your output consistent for a reporting job
SET TIME ZONE 'UTC';

SELECT created_at, date_trunc('day', created_at) AS day_bucket
FROM events;

Pour tout ce qui produit des rapports ou des réponses d'API, explicitez la zone temporelle. Définissez-la à la connexion (ou exécutez SET TIME ZONE en premier), choisissez une norme pour les sorties machine (souvent UTC), et pour les rapports en « heure métier locale » définissez la zone métier dans le job, pas sur l'ordinateur de quelqu'un. Si vous utilisez des connexions poolées, réinitialisez les paramètres de session quand une connexion est empruntée.

Comment les dashboards cassent : regroupements, buckets et trous DST

Les dashboards paraissent simples : compter les commandes par jour, afficher les inscriptions par heure, comparer les semaines. Les problèmes commencent quand la base stocke un « moment » mais que le graphique le transforme en plusieurs « jours », selon qui regarde.

Si vous regroupez par jour en utilisant la zone locale d'un utilisateur, deux personnes peuvent voir des dates différentes pour le même événement. Une commande passée à 23:30 à Los Angeles est déjà « le lendemain » à Berlin. Et si votre SQL regroupe par DATE(created_at) sur un TIMESTAMP simple, vous ne regroupez pas par un instant réel. Vous regroupez par une lecture d'horloge murale sans zone attachée.

Les graphiques horaires deviennent plus compliqués autour du DST. Au printemps, une heure locale n'a jamais lieu, donc les graphiques peuvent montrer un trou. En automne, une heure locale se produit deux fois, donc vous pouvez obtenir un pic ou des buckets doublés si votre requête et votre dashboard ne s'accordent pas sur lequel des deux 01:30 vous voulez.

Une question pratique aide : tracez-vous des instants réels (sûrs à convertir), ou une heure de planning locale (à ne pas convertir) ? Les dashboards veulent presque toujours des instants réels.

Quand regrouper en UTC ou en zone métier

Choisissez une règle de regroupement et appliquez-la partout (SQL, API, outil BI), sinon les totaux dériveront.

Groupez en UTC quand vous voulez une série globale et cohérente (santé du système, trafic API, inscriptions globales). Groupez en zone métier quand « le jour » a une signification légale ou opérationnelle (journée de magasin, SLA support, clôture financière). Groupez par la zone du visionneur seulement quand la personnalisation est plus importante que la comparabilité (flux d'activité personnels).

Voici le modèle pour un regroupement « jour métier » cohérent :

SELECT date_trunc('day', created_at AT TIME ZONE 'America/New_York') AS business_day,
       count(*)
FROM orders
GROUP BY 1
ORDER BY 1;

Étiquettes qui évitent la méfiance

Les gens cessent de faire confiance aux graphiques quand les chiffres sautent et que personne ne peut expliquer pourquoi. Étiquetez clairement la règle directement dans l'interface : « Daily orders (America/New_York) » ou « Hourly events (UTC) ». Utilisez la même règle dans les exports et les API.

Un petit ensemble de règles pour le reporting et les API

Décidez si vous stockez un instant ou une lecture murale locale. Mélanger les deux est là où dashboards et API commencent à diverger.

Un jeu de règles qui rend le reporting prévisible :

• Stockez les événements réels comme instants en utilisant TIMESTAMPTZ, et traitez UTC comme source de vérité.
• Stockez les concepts métier comme « billing day » séparément en tant que DATE (ou un champ de temps local si vous avez vraiment besoin de l'heure murale).
• Dans les API, renvoyez les timestamps en ISO 8601 et soyez cohérent : incluez toujours un offset (comme +02:00) ou utilisez Z pour UTC.
• Convertissez aux frontières (UI et couche de reporting). Évitez de reconvertir sans cesse dans la logique DB et les jobs en arrière-plan.

Pourquoi cela tient : les dashboards bucketent et comparent des plages. Si vous stockez des instants (TIMESTAMPTZ), PostgreSQL peut ordonner et filtrer les événements de manière fiable même quand le DST change. Ensuite, vous choisissez comment les afficher ou les grouper. Si vous stockez une heure murale (TIMESTAMP) sans zone, PostgreSQL ne peut pas savoir ce que cela signifie, donc le regroupement peut changer si la zone de session change.

Gardez les « dates métier locales » séparées car ce ne sont pas des instants. « Livrer le 2026-03-08 » est une décision de date, pas un instant. Si vous le forcez dans un timestamp, les jours DST peuvent créer des heures locales manquantes ou dupliquées, qui réapparaîtront ensuite comme des trous ou des pics.

Étape par étape : choisir le bon type pour chaque valeur temporelle

Choisir entre TIMESTAMPTZ et TIMESTAMP commence par une question : cette valeur décrit-elle un vrai moment qui s'est produit, ou une heure locale que vous voulez garder telle quelle ?

1) Séparez événements réels et heures locales planifiées

Faites un inventaire rapide de vos colonnes.

Les événements réels (clics, paiements, connexions, envois, relevés de capteurs, messages support) doivent généralement être stockés en TIMESTAMPTZ. Vous voulez un instant sans ambiguïté, même si les gens le voient depuis des fuseaux différents.

Les horaires locaux planifiés sont différents : « le magasin ouvre à 09:00 », « fenêtre de pickup 16:00–18:00 », « la facturation s'exécute le 1er à 10:00 heure locale ». Ceux-ci sont souvent mieux en TIMESTAMP plus un champ de zone horaire séparé, car l'intention est liée à l'horloge murale d'un lieu.

2) Choisissez une norme et documentez-la

Pour la plupart des produits, un bon défaut est : stocker les temps d'événements en UTC, les présenter dans la zone de l'utilisateur. Documentez cela là où les gens lisent vraiment : notes de schéma, docs API, descriptions des dashboards. Définissez aussi ce que « jour métier » signifie (jour UTC, jour en zone métier, ou jour local du visionneur), car ce choix pilote le reporting quotidien.

Une checklist courte qui marche en pratique :

• Marquez chaque colonne temporelle comme « instant d'événement » ou « planning local ».
• Par défaut, stockez les instants d'événement en TIMESTAMPTZ en UTC.
• Lors des modifications de schéma, backfillez prudemment et validez des lignes d'exemple à la main.
• Standardisez les formats d'API (inclure toujours Z ou un offset pour les instants).
• Définissez explicitement la zone de session dans les jobs ETL, connecteurs BI et workers en arrière-plan.

Soyez prudent avec le travail de « convertir et backfiller ». Changer un type de colonne peut silencieusement modifier le sens si les anciennes valeurs ont été interprétées sous une autre zone de session.

Erreurs courantes qui provoquent des décalages d'un jour et des bugs DST

La plupart des bugs temporels ne sont pas « PostgreSQL qui fait des siennes ». Ils viennent du fait de stocker la valeur qui a l'air correcte avec le mauvais sens, puis de laisser différentes couches deviner le contexte manquant.

Erreur 1 : Enregistrer une heure murale comme si c'était absolu

Un piège courant est de stocker une heure locale (comme « 2026-03-29 09:00 » à Berlin) dans un TIMESTAMPTZ. PostgreSQL le traite comme un instant et le convertit selon la zone de session courante. Si l'intention était « toujours 9h locale », vous venez de la perdre. Afficher la même ligne sous une autre zone de session décalera l'heure affichée.

Pour les rendez-vous, stockez l'heure locale en TIMESTAMP plus une zone (ou lieu). Pour les événements qui ont eu lieu à un instant (paiements, connexions), stockez l'instant en TIMESTAMPTZ.

Erreur 2 : Environnements différents, hypothèses différentes

Votre laptop, staging et production peuvent ne pas partager la même zone horaire. Un environnement tourne en UTC, un autre en heure locale, et les rapports « group by day » commencent à diverger. Les données n'ont pas changé, le réglage de session l'a fait.

Erreur 3 : Utiliser des fonctions temporelles sans connaître leurs garanties

now() et current_timestamp sont stables dans une transaction. clock_timestamp() change à chaque appel. Si vous générez des timestamps à plusieurs points dans une transaction et mélangez ces fonctions, l'ordre et les durées peuvent paraître bizarres.

Erreur 4 : Convertir deux fois (ou pas du tout)

Un bug d'API fréquent : l'app convertit une heure locale en UTC, l'envoie comme chaîne naïve, puis la BDD la reconvertit parce qu'elle suppose que l'entrée était locale. L'inverse arrive aussi : l'app envoie une heure locale mais la marque avec Z (UTC), la décalant à l'affichage.

Erreur 5 : Grouper par date sans préciser la zone horaire voulue

« Totaux journaliers » dépend de la frontière de journée que vous entendez. Si vous groupez avec date(created_at) sur un TIMESTAMPTZ, le résultat suit la zone de session. Les événements tardifs peuvent passer au jour précédent ou suivant.

Avant de livrer un dashboard ou une API, vérifiez les bases : choisissez une zone de reporting par graphique et appliquez-la de façon cohérente, incluez les offsets (ou Z) dans les payloads API, alignez staging et production sur la politique de fuseau, et soyez explicite sur la zone utilisée lors du regroupement.

Vérifications rapides avant de livrer un dashboard ou une API

Les bugs temporels viennent rarement d'une seule mauvaise requête. Ils apparaissent parce que le stockage, le reporting et l'API font chacun une hypothèse légèrement différente.

Utilisez une courte checklist pré-livraison :

• Pour les événements réels (inscriptions, paiements, pings capteurs), stockez l'instant en TIMESTAMPTZ.
• Pour les concepts métier locaux (date de facturation, date de reporting), stockez un DATE ou un TIME, pas un timestamp que vous comptez « convertir plus tard ».
• Dans les jobs planifiés et les exécutants de rapports, définissez la zone de session volontairement.
• Dans les réponses d'API, incluez un offset ou Z, et confirmez que le client le parse comme conscient de la zone.
• Testez la semaine de transition DST pour au moins une zone cible.

Une validation rapide bout en bout : choisissez un cas limite connu (par exemple 2026-03-08 01:30 dans une zone observant DST) et suivez-le depuis le stockage, la sortie de requête, le JSON d'API, jusqu'à l'étiquette finale du graphique. Si le graphique montre le bon jour mais l'infobulle affiche la mauvaise heure (ou inversement), vous avez un décalage de conversion.

Exemple : pourquoi deux équipes ne sont pas d'accord sur les mêmes chiffres du jour

Une équipe support à New York et une équipe finance à Berlin regardent le même tableau de bord. Le serveur de base tourne en UTC. Tout le monde est convaincu d'avoir raison, mais « hier » varie selon qui on interroge.

Voici l'événement : un ticket client est créé à 23:30 à New York le 10 mars. C'est 04:30 UTC le 11 mars, et 05:30 à Berlin. Un même instant, trois dates calendaires différentes.

Si l'heure de création est stockée en TIMESTAMP (sans zone) et que votre app suppose que c'est « local », vous pouvez réécrire l'histoire silencieusement. New York traitera 2026-03-10 23:30 comme heure de New York, tandis que Berlin interprétera la même valeur stockée comme heure de Berlin. La même ligne tombe sur des jours différents selon le visionneur.

Si elle est stockée en TIMESTAMPTZ, PostgreSQL conserve l'instant de façon cohérente et ne le convertit que lorsque quelqu'un l'affiche ou le formate. C'est pourquoi TIMESTAMPTZ vs TIMESTAMP change la signification de « un jour » dans les rapports.

La solution est de séparer deux idées : l'instant où l'événement s'est produit, et la date de reporting que vous voulez utiliser.

Un pattern pratique :

1. Stockez l'heure de l'événement en TIMESTAMPTZ.
2. Décidez de la règle de reporting : locale du visionneur (dashboards personnels) ou zone métier unique (finance globale).
3. Calculez la date de reporting à la requête : convertissez l'instant dans la zone choisie, puis prenez la date.

Prochaines étapes : standardiser la gestion du temps dans toute la stack

Si la gestion du temps n'est pas écrite, chaque nouveau rapport devient un jeu de devinettes. Visez un comportement temporel ennuyeux et prévisible à travers la base, les API et les dashboards.

Rédigez un petit « contrat temporel » qui répond à trois questions :

• Standard des temps d'événement : stocker les instants d'événements en TIMESTAMPTZ (généralement en UTC) sauf raison forte de ne pas le faire.
• Zone métier : choisissez une zone pour le reporting et utilisez-la de manière cohérente quand vous définissez « jour », « semaine » et « mois ».
• Format API : envoyez toujours des timestamps avec un offset (ISO 8601 avec Z ou +/-HH:MM) et documentez si les champs signifient « instant » ou « heure murale locale ».

Ajoutez des tests autour du début et de la fin du DST. Ils détectent tôt des bugs coûteux. Par exemple, validez qu'une requête de « total journalier » est stable pour une zone métier fixe à travers un changement DST, et que des entrées API comme 2026-11-01T01:30:00-04:00 et 2026-11-01T01:30:00-05:00 sont traitées comme deux instants différents.

Planifiez soigneusement les migrations. Changer les types et les hypothèses en place peut réécrire silencieusement l'histoire dans les graphiques. Une approche plus sûre est d'ajouter une nouvelle colonne (par exemple created_at_utc TIMESTAMPTZ), de la backfiller avec une conversion revue, de mettre à jour les lectures pour utiliser la nouvelle colonne, puis de mettre à jour les écritures. Conservez anciens et nouveaux rapports côte à côte brièvement pour que les décalages deviennent évidents.

Si vous voulez un endroit unique pour appliquer ce « contrat temporel » dans les modèles de données, les API et les écrans, une configuration de build unifiée aide. AppMaster génère backend, web app et API depuis un projet unique, ce qui facilite le maintien cohérent des règles de stockage et d'affichage des timestamps à mesure que votre application grandit.