date_trunc()

Truncate to specified precision; see Section 9.9.2

Signature

date_trunc ( text, timestamp ) → timestamp
date_trunc ( text, timestamp with time zone, text ) → timestamp with time zone
date_trunc ( text, interval ) → interval

Examples

SELECT date_trunc('hour', timestamp '2001-02-16 20:38:40');

2001-02-16 20:00:00

SELECT date_trunc('hour', interval '2 days 3 hours 40 minutes');

2 days 03:00:00

Run against PostgreSQL 18.6 on 2026-09-13. This is the output we got, not the documentation’s.

In other engines

PostgreSQL	date_trunc
MySQL	DATE_FORMAT(x, '%Y-%m-01') and friends
SQL Server	DATETRUNC(month, x) on SQL Server 2022+, else DATEADD/DATEDIFF
Oracle	TRUNC(x, 'MM')
SQLite	strftime('%Y-%m-01', x)

Only PostgreSQL and recent SQL Server have a single function for this. The DATEADD(month, DATEDIFF(month, 0, x), 0) idiom you will find in older SQL Server code is doing the same job the long way.

Want to run this against your own PostgreSQL server from an iPhone, iPad or Mac? These are ours — every connection goes straight from the device to your database, with no account and no server of ours in between.

Postgres & FriendsPostgreSQL and the open-source engines around it

Signature and description adapted from the PostgreSQL manual, used under the PostgreSQL License. PostgreSQL is a registered trademark of the PostgreSQL Community Association of Canada. This page is not affiliated with the PostgreSQL Global Development Group.