How to Use date_trunc in PostgreSQL: Syntax and Examples

In PostgreSQL, use the date_trunc function to truncate a timestamp or interval to a specified precision such as 'hour', 'day', or 'month'. It returns the timestamp rounded down to the start of the specified unit.

Syntax

The date_trunc function takes two arguments: the precision to truncate to, and the timestamp or interval value. The precision is a string like 'second', 'minute', 'hour', 'day', 'month', 'year', etc.

It returns the timestamp truncated to the start of that precision.

sql

date_trunc('precision', timestamp_value)

Example

This example shows how to truncate a timestamp to the hour and to the day.

sql

SELECT
  date_trunc('hour', TIMESTAMP '2024-06-15 14:37:22') AS truncated_to_hour,
  date_trunc('day', TIMESTAMP '2024-06-15 14:37:22') AS truncated_to_day;

Output

 truncated_to_hour    | truncated_to_day 
---------------------+------------------
 2024-06-15 14:00:00 | 2024-06-15 00:00:00

Common Pitfalls

• Using an invalid precision string will cause an error. Always use supported units like 'second', 'minute', 'hour', 'day', 'month', or 'year'.
• Passing a date instead of a timestamp works but returns a timestamp at midnight of that date.
• Remember that date_trunc truncates down, it does not round.

sql

/* Wrong: invalid precision */
-- SELECT date_trunc('weeks', TIMESTAMP '2024-06-15 14:37:22'); -- ERROR

/* Right: use 'week' */
SELECT date_trunc('week', TIMESTAMP '2024-06-15 14:37:22');

Output

 date_trunc 
------------
 2024-06-10 00:00:00

Quick Reference

Precision	Description
second	Truncates to the start of the second
minute	Truncates to the start of the minute
hour	Truncates to the start of the hour
day	Truncates to midnight of the day
week	Truncates to the start of the week (Sunday)
month	Truncates to the first day of the month
quarter	Truncates to the first day of the quarter
year	Truncates to the first day of the year

Key Takeaways

Use date_trunc to round down timestamps to a specified precision like hour or day.

The first argument is the precision string; the second is the timestamp or interval.

Supported precisions include 'second', 'minute', 'hour', 'day', 'week', 'month', 'quarter', and 'year'.

date_trunc always truncates down; it does not round up.

Invalid precision strings cause errors, so use only supported units.