DATE_TRUNC
Truncates a timestamp or interval down to the given unit.
PostgreSQL 17.5
DATE_TRUNC(field, source[, time_zone])Parameters
- fieldtext
- Unit to truncate to
- Values:'microseconds''milliseconds''second''minute''hour''day''week''month''quarter''year''decade''century''millennium'
- sourcetimestamp | interval
- Timestamp or interval
- time_zonetextoptional
- Time zone for a timestamptz value, from the TimeZone setting by default
Return value
A value of the same type as source with the smaller parts reset: for month, the first day of the month at 00:00; timestamptz for date
Examples
Start of the month for a date
PostgreSQL 17.5
SELECT DATE_TRUNC('month', TIMESTAMP '2023-02-15 10:20:30');Monday of the same week
PostgreSQL 17.5
SELECT DATE_TRUNC('week', TIMESTAMP '2024-03-08 14:30:00');Payment totals by month
PostgreSQL 17.5
SELECT DATE_TRUNC('month', date) AS MONTH,
SUM(amount * unit_price) AS total
FROM Payments
GROUP BY DATE_TRUNC('month', date)
ORDER BY MONTH;Details
A week ('week') starts on Monday. For a date value the result is timestamptz: to get a date back, add ::date. The function is handy for grouping by period: GROUP BY DATE_TRUNC('month', date).