DATE_TRUNC

Truncate a timestamp down to the start of a specified unit (year, month, day, hour, etc.).

Category: datetimeReturns: TIMESTAMPDialect: Standard

Syntax

DATE_TRUNC(unit, timestamp)

Description

## Overview Truncates a TIMESTAMP (or DATE) to the start of the specified unit: all fields less significant than the unit are set to their minimum values. Truncating to 'month' returns the first day of the containing month at 00:00:00; truncating to 'hour' returns the same date with minute and second zeroed. Use DATE_TRUNC for time-series rollups, period-start calculations, and any query that groups by a uniform time bucket (hour, day, week, month, quarter, year). ## Behavior - Returns a TIMESTAMP (or DATE when the input and unit are both date-level). - Returns NULL if either argument is NULL. - Supported units: year, quarter, month, week, day, hour, minute, second, millisecond, microsecond. - Truncation is a floor operation: the result is always less than or equal to the input. - For 'week', the result is the Monday of the containing ISO week. - For 'quarter', the result is the first day of the containing calendar quarter (January 1, April 1, July 1, or October 1). - Output preserves the input's time zone type: TIMESTAMP WITH TIME ZONE in, TIMESTAMP WITH TIME ZONE out. ## Timezone handling - For TIMESTAMP WITH TIME ZONE, the truncation is performed in the session zone. Truncating the same instant in a different session zone can produce a different 'day' start. - To truncate in a specific zone regardless of session, use (ts AT TIME ZONE 'zone_name') first. - DST transitions can shift the wall-clock start-of-day by an hour; DATE_TRUNC('day', ...) always returns midnight in the session zone, but that midnight may be a different instant on DST days. ## Compatibility - Unit-based truncation matching the widely adopted DATE_TRUNC convention. Week truncation uses ISO 8601 (Monday-start).

Parameters

Name	Type	Description
`unit`		Specifies the truncation precision. Accepts 'year', 'quarter', 'month', 'week', 'day', 'hour', 'minute', 'second', 'millisecond', 'microsecond'. Case-insensitive.
`timestamp`		Specifies the timestamp (or date) value to truncate.

Examples

-- Truncate to the start of the month
SELECT DATE_TRUNC('month', TIMESTAMP '2025-03-15 14:30:45') AS month_start;

-- Truncate to the start of the year
SELECT DATE_TRUNC('year', TIMESTAMP '2025-07-04 09:00:00') AS year_start;

-- Truncate to the hour
SELECT DATE_TRUNC('hour', TIMESTAMP '2025-03-15 14:37:22') AS hour_bucket;

-- Weekly revenue rollup (ISO weeks start Monday)
SELECT DATE_TRUNC('week', order_ts) AS week_start, SUM(total_amount) AS revenue
FROM commerce.sales.orders
GROUP BY week_start
ORDER BY week_start;

-- Truncate to the quarter
SELECT DATE_TRUNC('quarter', TIMESTAMP '2025-08-20 10:15:00') AS quarter_start;

-- Hourly event counts for the last day
SELECT DATE_TRUNC('hour', event_ts) AS hr, COUNT(*) AS events
FROM telemetry.web.requests
WHERE event_ts >= NOW() - INTERVAL '24 hours'
GROUP BY hr
ORDER BY hr;

Pitfalls

Week truncation uses ISO 8601 (Monday as first day of week). Applications that expect Sunday-start weeks must adjust with (DATE_TRUNC('week', ts) - INTERVAL '1 day') or use an explicit DATE_BIN with a Sunday-aligned origin.
For TIMESTAMP WITH TIME ZONE, the truncation is session-zone dependent. A report run at 01:00 UTC on January 1 will show one 'day' bucket in a UTC session and a different one in a session running in New York time.
Truncating across a DST transition: DATE_TRUNC('day', ts) returns local midnight, but the elapsed seconds between two consecutive day-start values can be 23, 24, or 25 hours on DST days.
Truncating to 'week' with a DATE input returns a DATE (the Monday); the time-of-day component is not introduced. Be aware of return-type differences when chaining.