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 |
QUARTER
|
The calendar quarter of the specified date as an integer, where the January-March quarter is 1. |
MONTH
|
For |
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)