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 lit02: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 |
Arithmétique des timestamps et intervalles
L’un des aspects les plus pratiques des timestamps PostgreSQL, c’est l’arithmétique d’intervalles. Comme les deux types stockent un nombre de microsecondes, on peut ajouter et soustraire des intervalles directement :
-- Ajouter 3 heures et 30 minutes
SELECT '2025-07-29 10:00'::timestamptz + INTERVAL '3 hours 30 minutes';
-- → 2025-07-29 13:30:00+08
-- Calculer l'écart entre deux timestamps
SELECT '2025-07-30 09:00'::timestamptz - '2025-07-29 10:00'::timestamptz;
-- → 23:00:00 (un intervalle)
-- Extraire un champ précis
SELECT EXTRACT(EPOCH FROM '2025-07-29 10:00:00+08'::timestamptz);
-- → 1753754400.000000 (timestamp Unix en secondes ; type numeric depuis PostgreSQL 14)
-- Tronquer au début de la journée (pratique pour les agrégations quotidiennes)
SELECT date_trunc('day', '2025-07-29 15:42:19+08'::timestamptz);
-- → 2025-07-29 00:00:00+08La fonction EXTRACT(EPOCH FROM ...) est particulièrement utile pour transmettre des timestamps à des systèmes externes qui attendent des secondes epoch Unix. À l’inverse, on peut reconvertir un epoch en timestamp :
SELECT to_timestamp(1753754400);
-- → 2025-07-29 10:00:00+08 (dans une session Asia/Shanghai)Point subtil mais important : l’arithmétique d’intervalles sur timestamp (sans fuseau) ignore complètement les changements d’heure, alors que timestamptz en tient compte. Ajouter INTERVAL '1 day' à une valeur timestamptz qui franchit un changement d’heure renvoie donc correctement la même heure murale, et non exactement 24 heures plus tard.
Indexation et performances
timestamp et timestamptz sont tous deux stockés sous forme d’entiers de 8 octets : aucune différence de performance entre eux, ni pour le stockage ni pour l’indexation. Les index B-tree fonctionnent exactement de la même façon sur les deux types, puisque la comparaison sous-jacente n’est qu’une comparaison d’entiers.
Quelques points pratiques méritent toutefois votre attention :
- Requêtes par plage :
WHERE created_at > '2025-07-01's’exécute efficacement avec un index, quel que soit le type. Avectimestamptz, PostgreSQL convertit le littéral en UTC avant la comparaison : l’index reste donc utilisé. - Clés de partitionnement : pour un partitionnement par plage sur une colonne horodatée,
timestamptzest généralement plus sûr, car les bornes de partition sont sans ambiguïté (toujours en UTC). Avectimestamp, une borne comme'2025-07-01 00:00'peut prendre un sens différent d’une session à l’autre. - Index fonctionnels : si vous filtrez souvent sur la date seule (sans l’heure), envisagez un index sur
date_trunc('day', created_at)pour accélérer les agrégations quotidiennes.
Pièges fréquents & remèdes rapides
1. Chaque utilisateur a sa montre
- Cause : chaque client définit son propre
TimeZoneavectimestamptz - Remède : soit tout passer en
timestampavec un fuseau de référence, soit forcerSET TimeZone = 'UTC'à la connexion
Côté code applicatif, un schéma courant consiste à fixer le fuseau une seule fois, à l’initialisation du pool de connexions :
-- Dans la configuration de connexion (par ex. la config du pool pg)
SET timezone = 'UTC';Toutes les sessions voient ainsi la même représentation UTC, et c’est la couche applicative qui convertit en heure locale pour l’affichage.
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
Le test est simple : si la question est « à quel moment cela s’est-il produit ? », utilisez timestamptz. Si la question est « quelle heure indique l’horloge murale ? », utilisez timestamp.
3. APIs qui dérivent
- Expédiez toujours les
timestamptzen ISO 8601 avec l’offset (Zou+08:00) - Le front formate selon le navigateur de l’utilisateur
4. Comparer des timestamps de types différents
Mélanger timestamp et timestamptz dans des comparaisons ou des jointures est une source fréquente de bugs subtils :
-- Dangereux : le cast implicite applique le fuseau de la session
SELECT * FROM orders o
JOIN schedules s ON o.created_tz = s.start_ts;
-- PostgreSQL convertit s.start_ts en timestamptz avec le fuseau de la session
-- Deux sessions différentes peuvent obtenir des résultats de jointure différents !Remède : castez toujours explicitement quand vous comparez des types différents, ou standardisez un seul type par domaine.
5. Les valeurs par défaut piégeuses des ORM
De nombreux ORM (Django, SQLAlchemy, ActiveRecord) utilisent par défaut timestamp sans fuseau. Vérifiez vos fichiers de migration : si votre application sert des utilisateurs répartis sur plusieurs fuseaux, remplacez ce défaut par timestamptz. Dans Django, définissez USE_TZ = True dans les settings. Dans SQLAlchemy, utilisez DateTime(timezone=True).
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 1753754400, 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.
Pour un guide complet sur les timestamps Unix (gestion de la précision, bonnes pratiques de fuseau horaire, exemples de code en JavaScript, Python et Go), consultez notre guide des timestamps Unix.
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