DATE_TRUNC()
Truncates a date/timestamp down to the start of a given unit.
Description
DATE_TRUNC rounds a date or timestamp down to the beginning of the
specified unit — the start of the year, month, day, hour, etc. It's the standard
way to bucket rows by time period for grouping (e.g. sales per month).
Syntax
DATE_TRUNC('month', source)
DATE_TRUNC('day', source)Parameters
| Name | Description | Optional |
|---|---|---|
| unit | The truncation unit: year, quarter, month, week, day, hour, minute, second. | No |
| source | The date/timestamp to truncate. | No |
Return Type
Returns a TIMESTAMP (or DATE if the input is a date).
Examples
Employees & Departments
Loading database engine...
Employees & Departments
Loading database engine...
Group by period
GROUP BY DATE_TRUNC('month', hire_date) gives clean per-month buckets
without string formatting.
Common Mistakes
- Wrong unit name. Units are engine-specific strings;
'mm'vs'month'differs by database. - Expecting rounding up.
DATE_TRUNCalways goes down to the unit start. - Mixing date and timestamp. The result type follows the input; a DATE stays a DATE.
Related Functions
See also: EXTRACT, DATEDIFF, NOW.