DATE_TRUNC

All functions > DATE AND TIME > DATE_TRUNC

Returns the given timestamp truncated to the given unit.

Signatures

Returns: The timestamp truncated to the specified unit

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

Notes

• Truncates 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
• Example: DATE_TRUNC(timestamp, 'day') returns midnight of that day

Examples

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
;