trunc

On this page

Truncates a timestamp to the start of a specified time unit.

Syntax

trunc(<timestamp>, unit => <unit>)

Arguments

ArgumentTypeDescription
<timestamp>timestampTimestamp to truncate.
unitstringConstant. Unit: 'hour', 'minute', 'second', 'millisecond', 'microsecond', or 'nanosecond'. Must not be NULL.

Returns

Returns timestamp, rounded down to the unit boundary in UTC. Returns NULL if the input is NULL.

Usage notes

To group by calendar day, use strftime, for example strftime(event_time, format => '%Y-%m-%d', timezone => 'UTC').

Examples

Truncate timestamps to their UTC hour before grouping. Events within the same hour share one bucket.

VALUES
    ('2026-06-01T10:02:00Z'), ('2026-06-01T10:48:00Z'),
    ('2026-06-01T11:05:00Z'), ('2026-06-01T11:12:00Z'), ('2026-06-01T11:56:00Z')
SELECT $0::timestamp AS event_time
GROUP BY trunc(event_time, unit => 'hour') AS hour
AGGREGATE count() AS checkouts
ORDER BY hour
+----------------------+-----------+
| hour                 | checkouts |
+----------------------+-----------+
| 2026-06-01T10:00:00Z | 2         |
| 2026-06-01T11:00:00Z | 3         |
+----------------------+-----------+