PostgreSQL timestamp vs timestamptz : que stocke réellement la base de données ?

PostgreSQL stocke à la fois timestamp et timestamptz sous forme d’un entier 64 bits représentant le nombre de microsecondes depuis le 1er janvier 1970 à 00:00:00 UTC. La différence réside uniquement dans l’étiquetage lors de l’affichage.

Pourquoi est-ce si trompeur ?

Deux colonnes peuvent afficher des résultats différents pour le même instant
Votre application insère 2025-07-29 10:00, mais une autre équipe lit 02:00
Les chaînes ISO du frontend ne correspondent pas aux logs du backend

Deux bocaux de pêches : l’un nu, l’autre étiqueté

Type SQL	Nom officiel	Valeur stockée	Comportement au SELECT
`timestamp`	timestamp without time zone	compteur de microsecondes brut	Restitué tel quel — Postgres ne devine jamais un fuseau
`timestamptz`	timestamp with time zone	même compteur	Postgres applique le `TimeZone` de session avant envoi

L’analogie

timestamp = un bocal de pêches sans étiquette d’origine. Vous savez que c’est du fruit, mais pas d’où ça vient.
timestamptz = un bocal fièrement tamponné « Made in UTC+8 ». Celui qui l’ouvre peut décider de convertir.

Sous le capot : un simple gros nombre

2000-01-01 00:00:00 UTC  → 0
2000-01-01 00:00:01 UTC  → 1 000 000

Unité : microseconde (un millionième de seconde)
Plage : 4713 av. J.-C. – 294 276 ap. J.-C.
Le stockage est identique pour les deux types ; seule l’interprétation change

Démo express : 15 secondes chrono

-- Le client pense en heure de Shanghai
SET TimeZone = 'Asia/Shanghai';

CREATE TABLE demo (
  created_ts timestamp,
  created_tz timestamptz
);

INSERT INTO demo VALUES ('2025-07-29 10:00', '2025-07-29 10:00');

Requête	Résultat	Explication
`SELECT created_ts FROM demo;`	2025-07-29 10:00:00	Valeur brute, sans calcul TZ
`SELECT created_tz FROM demo;`	2025-07-29 10:00:00+08	Étiquette appliquée à la sortie
Après `SET TimeZone = 'UTC';`	2025-07-29 02:00:00+00	Même instant, autre regard

Pièges fréquents & remèdes rapides

1. Chaque utilisateur a sa montre

Cause : chaque client définit son propre TimeZone avec timestamptz
Remède : soit tout passer en timestamp avec un fuseau de référence, soit forcer SET TimeZone = 'UTC' à la connexion

2. Conserver l’heure « murale » avec le mauvais type

Agendas locaux (horaires de magasin, échéances) → timestamp
Flux internationaux (commandes, logs) → stockez l’UTC en timestamptz

3. APIs qui dérivent

Expédiez toujours les timestamptz en ISO 8601 avec l’offset (Z ou +08:00)
Le front formate selon le navigateur de l’utilisateur

Mémo : quel type choisir ?

Calendrier local → timestamp
Besoin mondial   → timestamptz (stockez en UTC)

Rapports financiers, plannings de cours → timestamp
Logs d’audit, commandes e-commerce → timestamptz

Vérification rapide avec Go Tools

Besoin	Outil	Mode d’emploi
Inspecter la valeur epoch	Convertisseur d’époque	Coller `1690622400`, cliquer Convert
Nettoyer un JSON rempli de dates	Formateur JSON	Coller la charge, cliquer Embellir

Tous les utilitaires s’exécutent entièrement dans le navigateur — aucune donnée ne quitte votre machine.

Questions fréquentes

Quelle est la différence entre timestamp et timestamptz dans PostgreSQL ?

timestamp (sans fuseau horaire) stocke une valeur date-heure telle quelle, sans contexte de fuseau. timestamptz (avec fuseau horaire) convertit l’entrée en UTC pour le stockage et la reconvertit dans le fuseau de la session lors de la lecture. Utilisez timestamptz dans presque tous les cas — cela évite les bugs liés aux fuseaux dans les systèmes distribués.

PostgreSQL stocke-t-il réellement le fuseau horaire dans timestamptz ?

Non — malgré son nom, PostgreSQL ne stocke pas le fuseau horaire lui-même. Il convertit l’entrée en UTC et ne stocke que la valeur UTC (un compteur de microsecondes depuis le 01/01/2000). À la lecture, il convertit de l’UTC vers le fuseau spécifié par le paramètre timezone de la session. L’information de fuseau d’origine est perdue.

Comment changer le fuseau horaire d’une session PostgreSQL ?

Exécutez SET timezone = 'America/New_York'; pour modifier le fuseau de la session. Cela affecte l’affichage et l’interprétation des valeurs timestamptz. Pour les valeurs par défaut du serveur, configurez timezone dans postgresql.conf. Utilisez toujours les noms IANA (comme Europe/Paris) plutôt que les abréviations (comme CET) pour éviter toute ambiguïté.

Faut-il utiliser timestamp ou timestamptz pour stocker des horodatages d’événements ?

Utilisez timestamptz pour presque tout — actions utilisateur, appels API, journaux d’audit et événements planifiés. N’utilisez timestamp (sans fuseau) que pour des heures abstraites non liées à un instant précis, comme « le magasin ouvre à 09h00 », qui signifie 9h dans le fuseau local, pas un instant UTC spécifique.

Comment PostgreSQL gère-t-il l’heure d’été avec timestamptz ?

PostgreSQL gère correctement l’heure d’été avec timestamptz car il stocke tout en UTC en interne. Lors de la lecture, PostgreSQL convertit depuis l’UTC en appliquant les règles d’heure d’été en vigueur pour le fuseau de la session. Ainsi, le même instant UTC affiche correctement des heures locales différentes avant et après un changement d’heure.

En résumé

Les deux types temporels de Postgres sont des compteurs de microsecondes ; seule l’étiquette fait la différence
Se tromper de type mène à des horloges folles et des calculs erronés
Testez, convertissez et validez avec les bons outils pour gagner des heures de débogage

Que contient réellement une colonne timestamp PostgreSQL ?