How date_trunc() Works in PostgreSQL

by

In PostgreSQL, the date_trunc() function truncates a date/time value to a specified precision.

It can also truncate the value to a specified precision in a specified time zone.

You could think of it as a date version of the trunc() function (which truncates numbers).

Syntax

The syntax goes like this:

date_trunc(field, source [, time_zone ])

Where:

  • field is the precision for which to truncate the input value (for example month, hour, etc). See below for a full list of acceptable values for this argument.
  • source is a value expression of type timestamp, timestamp with time zone, or interval. Note that values of type date and time are cast automatically to timestamp or interval, respectively.
  • The optional time_zone argument can be provided to specify a different time zone.

The field argument can be any of the following:

  • microseconds
  • milliseconds
  • second
  • minute
  • hour
  • day
  • week
  • month
  • quarter
  • year
  • decade
  • century
  • millennium

Basic Example

Here’s an example to demonstrate.

SELECT date_trunc('hour', timestamp '2020-06-30 17:29:31');

Result:

2020-06-30 17:00:00

We can see that the time portion of the date has been truncated from 17:29:31 to 17:00:00. That’s because I used hour for the first argument.

Here it is with different values for the first argument.

\x
SELECT 
  date_trunc('minute', timestamp '2020-06-30 17:29:31'),
  date_trunc('day', timestamp '2020-06-30 17:29:31'),
  date_trunc('month', timestamp '2020-06-30 17:29:31'),
  date_trunc('year', timestamp '2020-06-30 17:29:31');

Result (using vertical output):

date_trunc | 2020-06-30 17:29:00
date_trunc | 2020-06-30 00:00:00
date_trunc | 2020-06-01 00:00:00
date_trunc | 2020-01-01 00:00:00

In this case I used \x to enable expanded display/vertical output, so that it’s easier to read the results.

With Time Zone

Here’s an example to demonstrate the WITH TIME ZONE option.

SELECT 
date_trunc('hour', timestamp with time zone '2020-06-30 17:29:31+00'),
date_trunc('hour', timestamp with time zone '2020-06-30 17:29:31+01');

Result:

date_trunc | 2020-07-01 03:00:00+10
date_trunc | 2020-07-01 02:00:00+10

The local timezone when I ran these examples was Australia/Brisbane.

You can also add the full timezone name as a third argument.

SELECT 
date_trunc('hour', timestamp with time zone '2020-06-30 17:29:31+12', 'Pacific/Auckland'),
date_trunc('hour', timestamp with time zone '2020-06-30 17:29:31+12', 'Pacific/Auckland');

Result:

date_trunc | 2020-06-30 15:00:00+10
date_trunc | 2020-06-30 15:00:00+10

With Interval

Here’s an example that uses an interval value instead of a date.

SELECT date_trunc('hour', interval '7 days 5 hours 15 minutes');

Result:

7 days, 5:00:00