DATE_TRUNC

Truncates date and time values to the specified precision. The return value is the same data type as the input value. All fields that are less than the specified precision are set to 0, or to 1 for day and month.

Behavior type

Syntax

DATE_TRUNC( precision, trunc-target )

Parameters

precision

A string constant that specifies precision for the truncated value. See Precision Field Values below. The precision must be valid for the trunc-target date or time.

trunc-target

Valid date/time expression.

Precision field values

MILLENNIUM

The millennium number.

CENTURY

The century number.

The first century starts at 0001-01-01 00:00:00 AD. This definition applies to all Gregorian calendar countries.

DECADE

The year field divided by 10.

YEAR

The year field. Keep in mind there is no 0 AD, so subtract BC years from AD years with care.

QUARTER

The calendar quarter of the specified date as an integer, where the January-March quarter is 1.

MONTH

For timestamp values, the number of the month within the year (1–12) ; for interval values the number of months, modulo 12 (0–11).

WEEK

The number of the week of the year that the day is in.

According to the ISO-8601 standard, the week starts on Monday, and the first week of a year contains January 4. Thus, an early January date can sometimes be in the week 52 or 53 of the previous calendar year. For example:

=> SELECT YEAR_ISO('01-01-2016'::DATE), WEEK_ISO('01-01-2016'), DAYOFWEEK_ISO('01-01-2016');
 YEAR_ISO | WEEK_ISO | DAYOFWEEK_ISO
----------+----------+---------------
     2015 |       53 |             5
(1 row)

DAY

The day (of the month) field (1–31).

HOUR

The hour field (0–23).

MINUTE

The minutes field (0–59).

SECOND

The seconds field, including fractional parts (0–59) (60 if leap seconds are implemented by the operating system).

MILLISECONDS

The seconds field, including fractional parts, multiplied by 1000. Note that this includes full seconds.

MICROSECONDS

The seconds field, including fractional parts, multiplied by 1,000,000. This includes full seconds.

Examples

The following example sets the field value as hour and returns the hour, truncating the minutes and seconds:

=> SELECT DATE_TRUNC('HOUR', TIMESTAMP '2012-02-24 13:38:40') AS HOUR;
        HOUR
---------------------
 2012-02-24 13:00:00
(1 row)

The following example returns the year from the input timestamptz '2012-02-24 13:38:40'. The function also defaults the month and day to January 1, truncates the hour:minute:second of the timestamp, and appends the time zone (-05):

=> SELECT DATE_TRUNC('YEAR', TIMESTAMPTZ '2012-02-24 13:38:40') AS YEAR;
          YEAR
------------------------
 2012-01-01 00:00:00-05
(1 row)

The following example returns the year and month and defaults day of month to 1, truncating the rest of the string:

=> SELECT DATE_TRUNC('MONTH', TIMESTAMP '2012-02-24 13:38:40') AS MONTH;
        MONTH
---------------------
 2012-02-01 00:00:00
(1 row)