PostgreSQL 18 reference · Date and Time Functions

PostgreSQL DATE_TRUNC() Function

Truncate a timestamp or interval to a unit such as 'month', 'week' or 'hour', for grouping by period.

PostgreSQL 18

Create a free account to save this page, take notes and track your progress. Sign in

Syntax

date_trunc(field, source, time_zone)

Parameter	Description
`field`	`'microseconds'` … `'second'`, `'minute'`, `'hour'`, `'day'`, `'week'`, `'month'`, `'quarter'`, `'year'`, `'decade'`, `'century'`.
`source`	A `date`, `timestamp`, `timestamptz` or `interval`.
`time_zone` optional	Truncate in this time zone (12+).

A timestamp, timestamptz or interval.

Truncate a timestamp

SELECT date_trunc('month', TIMESTAMP '2026-03-17 14:25') AS month_start,
       date_trunc('hour', TIMESTAMP '2026-03-17 14:25') AS hour_start;

Output

     month_start     |     hour_start
---------------------+---------------------
 2026-03-01 00:00:00 | 2026-03-17 14:00:00
(1 row)

Run this on your computer — see “Get Started” for setup.

Group by quarter

SELECT date_trunc('quarter', order_date)::date AS quarter, count(*) AS orders
FROM orders
GROUP BY quarter
ORDER BY quarter;

Output

  quarter   | orders
------------+--------
 2026-01-01 |      7
 2026-04-01 |      5
(2 rows)

Run this on your computer — see “Get Started” for setup.

EXTRACT() · Get one field from a date, time or interval, such as the year, month, day of week or epoch seconds.
DATE_BIN() · Round a timestamp down into fixed-size buckets, such as 15-minute slots, counted from an origin.
TO_CHAR() · Format a date, timestamp or number as text with a pattern such as 'DD Mon YYYY' or 'FM999,990.00'.
SQL Date Functions · tutorial