PostgreSQL date_part() Function

The PostgreSQL date_part() function extracts the specified field from the specified timestamp or interval and returns the result.

`date_part()` Syntax

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

date_part(field TEXT, source TIMESTAMP) -> DOUBLE PRECISION
date_part(field TEXT, source DATE) -> DOUBLE PRECISION
date_part(field TEXT, source TIME) -> DOUBLE PRECISION
date_part(field TEXT, source INTERVAL) -> DOUBLE PRECISION

Parameters

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

century: the century
day: the day field for timestamp values, or the number of days for interval values
decade: ten years, i.e. year divided by 10
dow: the day of the week. Sunday is 0, Saturday is 6.
doy: the day of the year (1–365/366)
epoch: Unix timestamp for timestamp values , or the total number of seconds for interval values.
hour: hours 0-23
isodow: the day of the week. Monday is 1, Sunday is 7.
isoyear: ISO 8601 week number year that the date falls in (not applicable to intervals)
julian: Julian date corresponding to date or timestamp (not applicable to intervals)
microseconds: the seconds multiplied by 1000000
millennium: the millennium
milliseconds: seconds multiplied by 1000
minute: the minutes field
month: the month of the year 1 - 12 for timestamp values, or the number of mouths (0 - 11) for interval values
quarter: the quarter of the year (1–4) that the date is in
second: the second filed
timezone: the time zone offset from UTC, measured in seconds
timezone_hour: the hour component of the time zone offset
timezone_minute: the minutes component of the timezone offset
week: the number of the ISO 8601 week-numbering week of the year
year: the year field

source: Required. It can be of timestamp, date, time or interval type.

Return value

The PostgreSQL date_part() function returns a field specified by the string field from source.

`date_part()` Examples

You can use the date_part() function to return the century that a timestamp value falls in, as follows:

SELECT date_part('century', TIMESTAMP '2022-05-16 12:41:13.662522');

 date_part
-----------
        21

You can use the date_part() function to get the day field of a timestamp value, as follows:

SELECT date_part('day', TIMESTAMP '2022-05-16 12:41:13.662522');

 date_part
-----------
        16

You can use the date_part() function to get the number of days from an interval value, as follows:

SELECT date_part('day', INTERVAL '2 days 10 minutes');

 date_part
-----------
         2

You can use the date_part() function to get the decade field from a timestamp value, as follows:

SELECT date_part('decade', TIMESTAMP '2022-05-16 12:41:13.662522');

 date_part
-----------
       202

You can use the date_part() function to get the day of the week (0-6 for Sunday to Saturday) from a timestamp value, as follows:

SELECT date_part('dow', TIMESTAMP '2022-05-16 12:41:13.662522');

 date_part
-----------
         1

You can use the date_part() function to get the day of the year from a timestamp value, as follows:

SELECT date_part('doy', TIMESTAMP '2022-05-16 12:41:13.662522');

 date_part
-----------
       136

You can use the date_part() function to convert a timestamp value to a Unix timestamp, as follows:

SELECT date_part('epoch', TIMESTAMP '2022-05-16 12:41:13.662522');

     date_part
-------------------
 1652694073.662522

You can use the date_part() function to get the hour field of a timestamp value. as follows:

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

 date_part
-----------
        12

You can use the date_part() function to get the day of the week (1-7 for Monday to Sunday) from a timestamp value, as follows:

SELECT date_part('isodow', TIMESTAMP '2022-05-16 12:41:13.662522');

 date_part
-----------
         1

You can use the date_part() function to get the ISO 8601 week number year from a timestamp value, as follows:

SELECT date_part('isoyear', TIMESTAMP '2022-05-16 12:41:13.662522');

 date_part
-----------
      2022

You can use the date_part() function to get the Julian date corresponding to a timestamp value, as follows:

SELECT date_part('julian', TIMESTAMP '2022-05-16 12:41:13.662522');

     date_part
-------------------
 2459716.528630353

You can use the date_part() function to convert full seconds to microseconds, as follows:

SELECT date_part('microseconds', TIMESTAMP '2022-05-16 12:41:13.662522');

 date_part
-----------
  13662522

You can use the date_part() function to get the millennium that a timestamp value falls in, as follows:

SELECT date_part('millennium', TIMESTAMP '2022-05-16 12:41:13.662522');

 date_part
-----------
         3

You can use the date_part() function to convert full seconds to milliseconds, as follows:

SELECT date_part('milliseconds', TIMESTAMP '2022-05-16 12:41:13.662522');

 date_part
-----------
 13662.522

You can use the date_part() function to get the minutes field of a timestamp value, as follows:

SELECT date_part('minute', TIMESTAMP '2022-05-16 12:41:13.662522');

 date_part
-----------
        41

You can use the date_part() function to get the month part of a timestamp value, as follows:

SELECT date_part('month', TIMESTAMP '2022-05-16 12:41:13.662522');

 date_part
-----------
         5

You can use the date_part() function to get the quarter of the year that a timestamp value falls in, as follows:

SELECT date_part('quarter', TIMESTAMP '2022-05-16 12:41:13.662522');

 date_part
-----------
         2

You can use the date_part() function to get the seconds field of a timestamp value. as follows:

SELECT date_part('second', TIMESTAMP '2022-05-16 12:41:13.662522');

 date_part
-----------
 13.662522

You can use the date_part() function to get the week of the year from a timestamp value, as follows:

SELECT date_part('week', TIMESTAMP '2022-05-16 12:41:13.662522');

 date_part
-----------
        20

You can use the date_part() function to get the year field of a timestamp value, as follows:

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

 date_part
-----------
      2022

date_part() Syntax

Parameters

Return value

date_part() Examples

On this page

`date_part()` Syntax

`date_part()` Examples