DATE_TRUNC()

All functions > DATE AND TIME > DATE_TRUNC()

Returns the given date or timestamp truncated to the given unit.

Signatures

Returns: The value truncated to the specified unit as a TIMESTAMP

DATE_TRUNC(timestamp: DATE | TIMESTAMP, unit: VARCHAR) → TIMESTAMP

Notes

• Truncates date or timestamp to the beginning of the specified unit
• Common units: 'second', 'minute', 'hour', 'day', 'week', 'month', 'quarter', 'year'
• Useful for grouping timestamps into time buckets
• Time components smaller than the unit are set to their minimum values
• DATE inputs are promoted to midnight TIMESTAMP before truncation
• Truncation uses the TIMESTAMP fields as-is (naive UTC if that is how values are stored). For calendar bucketing in a named zone, shift with UTC_TO_LOCAL / LOCAL_TO_UTC before or after truncating.

Examples

Date inputs

SELECT
    f1 := DATE_TRUNC(DATE '2024-03-15', 'month'), -- Date to start of month
    f2 := DATE_TRUNC(DATE '2024-03-15', 'year') -- Date to start of year
;

Timestamp inputs

SELECT
    f1 := DATE_TRUNC(FROM_ISO8601_TIMESTAMP('2024-03-15T10:30:45'), 'day'), -- Truncate to start of day
    f2 := DATE_TRUNC(FROM_ISO8601_TIMESTAMP('2024-03-15T10:30:45'), 'hour'), -- Truncate to start of hour
    f3 := DATE_TRUNC(FROM_ISO8601_TIMESTAMP('2024-03-15T10:30:45'), 'month') -- Truncate to start of month
;