PostgreSQL date_trunc: time zones and daily groups

PostgreSQL date_trunc reduces a timestamp to the start of a chosen unit, such as an hour, day, or month. For event reporting, the important question is which time zone defines that boundary. The same instant can fall into different calendar days in different zones.

We ran 38 checks on isolated PostgreSQL 18.6 using fixed timestamps and Europe/Zurich reporting dates. The examples cover plain timestamps, explicit reporting zones, daily and monthly groups, and the 2026 daylight-saving transitions. Every displayed result comes from the test run.

Start with a timestamp without a time zone

This input has calendar fields but no time-zone context. The result keeps that 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

The hour starts at 14:00, the month at January 1, and the week at Monday, December 29 of the previous year. Truncation does not round to the nearest boundary. Our separate December 31 test returned December 1 for month truncation, and a leap-day test stayed on February 29.

PostgreSQL’s date/time function reference lists supported units and signatures. This guide focuses on timestamp inputs. For fixed 15-minute buckets, consider date_bin with a chosen origin instead: our midnight-aligned test placed 14:37:52 in the 14:30 bucket.

Keep event instants separate from local labels

Use a disposable database where trunc_lab is unused. The following fixture stores eight event instants with explicit UTC offsets:

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 represents an instant; it does not preserve the original named time zone. Plain timestamp represents date and time fields without that instant context. The date/time type documentation explains the distinction.

Keep the reporting zone as an explicit business choice. Do not infer it from whichever zone the database connection happens to use. Our fixture deliberately includes events near local midnight and two different instants that share the same autumn wall-clock time.

See how the session zone changes two-argument truncation

For a timestamptz input, the two-argument call uses the session zone. The three-argument call below explicitly chooses Zurich. Both results are converted to UTC calendar fields for an unambiguous side-by-side display:

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

The first result row came from a UTC session, the second from a New York session. The default day boundary changed from March 29 at 00:00 UTC to March 28 at 04:00 UTC. The Zurich boundary stayed at March 28 at 23:00 UTC, which is midnight starting March 29 in Zurich.

The explicit-zone call still returns timestamptz. Its displayed offset can change with the session even though the underlying instant is unchanged. The TimeZone setting controls session interpretation and display; specifying a reporting zone does not change that setting.

Group events by the intended local day

Here we truncate in Zurich, convert the boundary back to Zurich calendar fields, and then extract the date label:

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

The March 29 group contains three events, including ID 2 at March 28, 23:00 UTC. In Zurich, that event is exactly the start of March 29. The totals across all five groups account for all eight fixture rows.

The conversion before ::date matters. In a UTC session, directly casting the Zurich midnight timestamptz for ID 2 to date produced March 28. Converting it to Zurich fields first produced the intended March 29 label. We verified the displayed grouping under UTC, New York, and Tokyo session settings.

Groups with no input events do not appear automatically. This example does not generate missing dates or fill them with zeros.

Group by month using local calendar fields

When the desired result is a local month label rather than a boundary instant, convert the event first, then truncate the resulting plain timestamp:

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

The fixture gives five March events and three October events. This query was also stable across all three session zones. The two approaches serve different outputs: a timestamptz boundary identifies an instant, while this date identifies a month in the named reporting calendar. Do not treat a local date label as a UTC instant without deciding how to convert it.

Calculate the next local midnight instead of adding 24 hours

These two Zurich dates cross daylight-saving changes. We construct each local midnight independently and measure the elapsed time between the resulting instants:

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

March 29 lasts 23 elapsed hours; October 25 lasts 25. The calculation uses the named zone’s rules in the tested PostgreSQL installation. Adding a fixed 24 hours to the March start did not reach the next local midnight: in our fixture, it incorrectly included an event from March 30.

These results concern the specified Zurich dates. They do not establish that every region changes clocks, uses the same transition dates, or changes by one hour. Keep time-zone data current and test the zones and dates your report actually uses.

Filter a day with an inclusive start and exclusive end

Use the local day’s two boundary instants to compare directly with the event column:

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

ID 2 is included at the exact start. ID 5, at the exact next midnight, is excluded. Changing this to inclusive BETWEEN returned four rows because it included ID 5. A start-inclusive, end-exclusive range keeps that boundary event in just one neighboring day.

This form leaves the event column unwrapped in the filter. It does not guarantee an index scan. We did not benchmark this eight-row table or test an index recommendation. Use the PostgreSQL EXPLAIN ANALYZE guide to inspect your actual query, remembering that EXPLAIN ANALYZE executes it. The pg_stat_statements guide can help identify reporting queries worth investigating.

Do not collapse repeated local times by accident

Two autumn events make the distinction between an instant and its local display visible:

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

Both local labels are 02:30, but the instants are an hour apart. Our check counted two distinct timestamptz values and only one distinct local timestamp. Converting to local fields discards the information that distinguishes those two occurrences.

For daily counts, combining them in the same local day is intentional. For an hourly timeline or event identity, keep the original instant or another key that preserves the distinction. If your source supplies only an ambiguous local time, this guide does not resolve which occurrence it meant.

Check boundaries, types, and cleanup

Beyond the displayed results, our checks covered NULL input, rejected units and zone names, leap day, return types, and an independent date-to-midnight calculation for every fixture event. The reporting queries preserved all eight source rows.

After the exercise, remove only the test objects:

DROP TABLE trunc_lab.events;
DROP SCHEMA trunc_lab;

The lab schema was removed and the isolated server stopped. Before adopting a report query, record its input type, reporting zone, grouping unit, and interval boundaries. Include an event at each boundary and, where relevant, a repeated local hour in the acceptance tests.