PostgreSQL date_trunc() Function

The PostgreSQL date_trunc() function truncates a specified timestamp or interval value to the specified part and returns the result.

date_trunc() Syntax

Here is the syntax of the PostgreSQL date_trunc() function:

date_trunc(field TEXT, source TIMESTAMP) -> TIMESTAMP
date_trunc(field TEXT, source TIMESTAMPTZ, time_zone TEXT) -> TIMESTAMPTZ
date_trunc(field TEXT, source INTERVAL) -> INTERVAL

Parameters

field

Required. It is a string representing the part to be truncated. You can use the following values:

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

Required. It can be of timestamp, timestamptz, or interval type.

time_zone

Optional. The time zone. Only accepted if source is of timestamptz type.

Return value

The PostgreSQL date_trunc() function truncates a specified timestamp or interval value to the specified part and returns the result.

date_trunc() Examples

The following example shows how to use the date_trunc() function to truncate a timestamp value to hour part, as follows:

SELECT date_trunc('hour', TIMESTAMP '2022-05-16 12:41:13.662522');

     date_trunc
---------------------
 2022-05-16 12:00:00

As shown in the results, the hours and above are preserved, while the minutes and seconds is truncated to 0.

The following example shows how to use the date_trunc() function to truncate a timestamp value to year part, as follows:

SELECT date_trunc('year', TIMESTAMP '2022-05-16 12:41:13.662522');

     date_trunc
---------------------
 2022-01-01 00:00:00

You can also use the date_trunc() function truncate a value of type interval, for example:

SELECT date_trunc('hour', INTERVAL '1 days 20:10:10');

   date_trunc
----------------
 1 day 20:00:00