PostgreSQL date_trunc : fuseaux et groupes par jour

Dans PostgreSQL, date_trunc ramène un horodatage au début d’une unité choisie : heure, jour ou mois, par exemple. Pour un rapport d’événements, il faut surtout déterminer quel fuseau définit cette limite. Un même instant peut appartenir à des jours calendaires différents selon le fuseau.

Nous avons exécuté 38 contrôles sur PostgreSQL 18.6 isolé, avec des horodatages fixes et des dates de rapport dans Europe/Zurich. Les exemples couvrent les horodatages sans fuseau, un fuseau de rapport explicite, les regroupements par jour et mois, ainsi que les changements d’heure de 2026. Tous les résultats affichés proviennent du test.

Commencer par un horodatage sans fuseau

Cette entrée contient des champs calendaires sans contexte de fuseau. Le résultat conserve ce type :

SELECT date_trunc('hour', timestamp '2026-01-01 14:37:52') AS hour_start,
       date_trunc('month', timestamp '2026-01-01 14:37:52') AS month_start,
       date_trunc('week', timestamp '2026-01-01 14:37:52') AS week_start;

2026-01-01 14:00:00|2026-01-01 00:00:00|2025-12-29 00:00:00

L’heure commence à 14 h, le mois au 1er janvier et la semaine au lundi 29 décembre de l’année précédente. La troncature n’arrondit pas à la limite la plus proche. Notre test distinct du 31 décembre a renvoyé le 1er décembre pour le mois ; le test du jour bissextile est resté au 29 février.

La référence des fonctions de date et d’heure liste les unités et signatures disponibles. Ce guide traite les horodatages. Pour des tranches fixes de 15 minutes, envisagez date_bin avec une origine définie : notre test aligné sur minuit a placé 14:37:52 dans la tranche de 14:30.

Séparer les instants des libellés locaux

Utilisez une base de test où trunc_lab n’existe pas. Ces données stockent huit instants avec des décalages UTC explicites :

CREATE SCHEMA trunc_lab;
CREATE TABLE trunc_lab.events (
    id integer PRIMARY KEY,
    occurred_at timestamptz NOT NULL
);
INSERT INTO trunc_lab.events VALUES
(1, '2026-03-28 22:59:59+00'),
(2, '2026-03-28 23:00:00+00'),
(3, '2026-03-29 00:30:00+00'),
(4, '2026-03-29 01:30:00+00'),
(5, '2026-03-29 22:00:00+00'),
(6, '2026-10-25 00:30:00+00'),
(7, '2026-10-25 01:30:00+00'),
(8, '2026-10-25 23:00:00+00');

timestamptz représente un instant, sans conserver le nom du fuseau d’origine. Le type timestamp simple représente des champs de date et d’heure sans ce contexte d’instant. La documentation des types temporels explique cette distinction.

Choisissez explicitement le fuseau du rapport selon le besoin métier. Ne le déduisez pas du fuseau utilisé par hasard dans la connexion. Nos données incluent des événements proches de minuit local et deux instants distincts qui partagent la même heure locale en automne.

Observer l’effet du fuseau de session sur la forme à deux arguments

Pour une entrée timestamptz, la forme à deux arguments utilise le fuseau de session. Celle à trois arguments choisit explicitement Zurich. Les deux résultats sont convertis en champs calendaires UTC pour faciliter la comparaison :

SET TIME ZONE 'UTC';
SELECT date_trunc('day', timestamptz '2026-03-29 01:30:00+00')
           AT TIME ZONE 'UTC' AS session_day_utc,
       date_trunc('day', timestamptz '2026-03-29 01:30:00+00', 'Europe/Zurich')
           AT TIME ZONE 'UTC' AS zurich_day_utc;
SET TIME ZONE 'America/New_York';
SELECT date_trunc('day', timestamptz '2026-03-29 01:30:00+00')
           AT TIME ZONE 'UTC' AS session_day_utc,
       date_trunc('day', timestamptz '2026-03-29 01:30:00+00', 'Europe/Zurich')
           AT TIME ZONE 'UTC' AS zurich_day_utc;
RESET TIME ZONE;

2026-03-29 00:00:00|2026-03-28 23:00:00
2026-03-28 04:00:00|2026-03-28 23:00:00

La première ligne provient d’une session UTC, la seconde d’une session New York. Le début du jour par défaut passe du 29 mars à 00:00 UTC au 28 mars à 04:00 UTC. La limite de Zurich reste le 28 mars à 23:00 UTC, soit minuit au début du 29 mars à Zurich.

L’appel avec fuseau explicite renvoie toujours un timestamptz. Son décalage affiché peut varier avec la session sans que l’instant change. Le paramètre TimeZone régit l’interprétation et l’affichage de la session ; préciser le fuseau du rapport ne modifie pas ce paramètre.

Regrouper les événements par jour local

Nous tronquons ici dans le fuseau de Zurich, reconvertissons la limite en champs calendaires de Zurich, puis extrayons la date :

SELECT (date_trunc('day', occurred_at, 'Europe/Zurich')
           AT TIME ZONE 'Europe/Zurich')::date AS reporting_day,
       count(*) AS events
FROM trunc_lab.events
GROUP BY 1
ORDER BY 1;

2026-03-28|1
2026-03-29|3
2026-03-30|1
2026-10-25|2
2026-10-26|1

Le groupe du 29 mars contient trois événements, dont l’ID 2 daté du 28 mars à 23:00 UTC. À Zurich, cet événement marque exactement le début du 29 mars. Les cinq groupes comptent ensemble les huit lignes du jeu de test.

La conversion avant ::date compte. Dans une session UTC, convertir directement en date le timestamptz de minuit à Zurich pour l’ID 2 donnait le 28 mars. Le convertir d’abord en champs de Zurich donnait le 29 mars attendu. Nous avons vérifié le regroupement affiché avec des sessions UTC, New York et Tokyo.

Les groupes sans événement ne sont pas créés automatiquement. Cet exemple ne génère pas les dates absentes et ne leur attribue pas de zéro.

Regrouper par mois à partir des champs locaux

Si le résultat attendu est un libellé de mois local plutôt qu’un instant de début, convertissez d’abord l’événement, puis tronquez le timestamp obtenu :

SELECT date_trunc('month', occurred_at AT TIME ZONE 'Europe/Zurich')::date
           AS reporting_month,
       count(*) AS events
FROM trunc_lab.events
GROUP BY 1
ORDER BY 1;

2026-03-01|5
2026-10-01|3

Les données donnent cinq événements en mars et trois en octobre. Cette requête reste stable dans les trois fuseaux de session. Les deux approches produisent des résultats de nature différente : une limite timestamptz identifie un instant, tandis que cette date identifie un mois dans le calendrier du rapport. Ne traitez pas un libellé local comme un instant UTC sans définir la conversion.

Calculer le minuit local suivant au lieu d’ajouter 24 heures

Ces deux dates de Zurich traversent un changement d’heure. Nous construisons chaque minuit local séparément et mesurons la durée entre les instants obtenus :

WITH days(d) AS (
    VALUES (date '2026-03-29'), (date '2026-10-25')
), bounds AS (
    SELECT d,
           d::timestamp AT TIME ZONE 'Europe/Zurich' AS start_at,
           (d + 1)::timestamp AT TIME ZONE 'Europe/Zurich' AS end_at
    FROM days
)
SELECT d, start_at AT TIME ZONE 'UTC' AS start_utc,
       end_at AT TIME ZONE 'UTC' AS end_utc,
       (extract(epoch FROM (end_at - start_at)) / 3600)::integer AS elapsed_hours
FROM bounds
ORDER BY d;

2026-03-29|2026-03-28 23:00:00|2026-03-29 22:00:00|23
2026-10-25|2026-10-24 22:00:00|2026-10-25 23:00:00|25

Le 29 mars dure 23 heures écoulées ; le 25 octobre en dure 25. Le calcul applique les règles du fuseau nommé dans l’installation PostgreSQL testée. Ajouter exactement 24 heures au début de la journée de mars ne menait pas au minuit local suivant : ce calcul incluait à tort un événement du 30 mars.

Ces résultats concernent les dates et le fuseau indiqués. Ils ne prouvent pas que chaque région change d’heure, aux mêmes dates ou d’une heure exactement. Maintenez les données de fuseaux à jour et testez les zones et dates utilisées par vos rapports.

Filtrer avec un début inclus et une fin exclue

Comparez directement la colonne des événements aux deux instants qui délimitent le jour local :

WITH bounds AS (
    SELECT timestamp '2026-03-29 00:00:00' AT TIME ZONE 'Europe/Zurich' AS start_at,
           timestamp '2026-03-30 00:00:00' AT TIME ZONE 'Europe/Zurich' AS end_at
)
SELECT e.id, e.occurred_at AT TIME ZONE 'UTC' AS occurred_utc
FROM trunc_lab.events AS e CROSS JOIN bounds AS b
WHERE e.occurred_at >= b.start_at
  AND e.occurred_at < b.end_at
ORDER BY e.id;

2|2026-03-28 23:00:00
3|2026-03-29 00:30:00
4|2026-03-29 01:30:00

L’ID 2 est inclus au début exact. L’ID 5, au minuit suivant exact, est exclu. La variante avec BETWEEN inclusif renvoyait quatre lignes, car elle incluait l’ID 5. Une borne de début incluse et une borne de fin exclue attribuent cet événement à une seule des journées voisines.

Ce filtre ne transforme pas la colonne des événements. Cela ne garantit pas un parcours d’index. Nous n’avons ni mesuré les performances de cette table de huit lignes ni testé une recommandation d’index. Le guide PostgreSQL EXPLAIN ANALYZE, en anglais aide à examiner votre requête ; EXPLAIN ANALYZE l’exécute. Le guide pg_stat_statements, en anglais aide à choisir les requêtes de rapport à étudier.

Ne pas fusionner involontairement les heures répétées

Deux événements d’automne rendent visible la différence entre un instant et son affichage local :

SELECT id, occurred_at AT TIME ZONE 'UTC' AS occurred_utc,
       occurred_at AT TIME ZONE 'Europe/Zurich' AS local_time
FROM trunc_lab.events
WHERE id IN (6, 7)
ORDER BY id;

6|2026-10-25 00:30:00|2026-10-25 02:30:00
7|2026-10-25 01:30:00|2026-10-25 02:30:00

Les deux libellés locaux indiquent 02:30, mais les instants sont séparés d’une heure. Notre contrôle a compté deux timestamptz distincts contre un seul timestamp local distinct. La conversion en champs locaux perd l’information permettant de distinguer ces deux occurrences.

Pour un comptage quotidien, les réunir dans le même jour local est intentionnel. Pour une chronologie horaire ou l’identité d’un événement, conservez l’instant d’origine ou une autre clé qui préserve la distinction. Si la source fournit seulement une heure locale ambiguë, ce guide ne détermine pas à quelle occurrence elle correspond.

Vérifier les limites, les types et le nettoyage

En plus des résultats affichés, nos tests couvrent une entrée NULL, des unités et fuseaux rejetés, le jour bissextile, les types renvoyés et un calcul indépendant du minuit pour chaque événement. Les requêtes ont conservé les huit lignes sources.

Après l’exercice, supprimez seulement les objets de test :

DROP TABLE trunc_lab.events;
DROP SCHEMA trunc_lab;

Le schéma a été supprimé et le serveur isolé arrêté. Avant d’adopter une requête de rapport, consignez le type d’entrée, le fuseau de référence, l’unité de regroupement et les bornes. Incluez un événement à chaque borne et, si nécessaire, une heure locale répétée dans vos tests d’acceptation.