PostgreSQL extract() Function

The PostgreSQL extract() function extracts the specified field from the specified timestamp or time interval and returns it.

`extract()` Syntax

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

extract(field FROM source TIMESTAMP) -> DOUBLE PRECISION
extract(field FROM source DATE) -> DOUBLE PRECISION
extract(field FROM source TIME) -> DOUBLE PRECISION
extract(field FROM source INTERVAL) -> DOUBLE PRECISION

Parameters

field: Required. It is a part of keywords to be extracted. You can use the following keywords:

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 extract() function returns a field specified by the string field from source.

`extract()` Examples

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

SELECT extract(CENTURY FROM TIMESTAMP '2022-05-16 12:41:13.662522');

 extract
---------
      21

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

SELECT extract(DAY FROM TIMESTAMP '2022-05-16 12:41:13.662522');

 extract
---------
      16

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

SELECT extract(DAY FROM INTERVAL '2 days 10 minutes');

 extract
---------
       2

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

SELECT extract(DECADE FROM TIMESTAMP '2022-05-16 12:41:13.662522');

 extract
---------
     202

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

SELECT extract(DOW FROM TIMESTAMP '2022-05-16 12:41:13.662522');

 extract
---------
       1

You can use the extract() function to get the day of the year, as follows:

SELECT extract(DOY FROM TIMESTAMP '2022-05-16 12:41:13.662522');

 extract
---------
     136

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

SELECT extract(EPOCH FROM TIMESTAMP '2022-05-16 12:41:13.662522');

      extract
-------------------
 1652704873.662522

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

SELECT extract(HOUR FROM TIMESTAMP '2022-05-16 12:41:13.662522');

 extract
---------
      12

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

SELECT extract(ISODOW FROM TIMESTAMP '2022-05-16 12:41:13.662522');

 extract
---------
       1

You can use the extract() function to get the ISO 8601 week number year, as follows:

SELECT extract(ISOYEAR FROM TIMESTAMP '2022-05-16 12:41:13.662522');

 extract
---------
    2022

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

SELECT extract(JULIAN FROM TIMESTAMP '2022-05-16 12:41:13.662522');

           extract
------------------------------
 2459716.52863035326388888889

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

SELECT extract(MICROSECONDS FROM TIMESTAMP '2022-05-16 12:41:13.662522');

 extract
----------
 13662522

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

SELECT extract(MILLENNIUM FROM TIMESTAMP '2022-05-16 12:41:13.662522');

 extract
---------
       3

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

SELECT extract(MILLISECONDS FROM TIMESTAMP '2022-05-16 12:41:13.662522');

  extract
-----------
 13662.522

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

SELECT extract(MINUTE FROM TIMESTAMP '2022-05-16 12:41:13.662522');

 extract
---------
      41

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

SELECT extract(MONTH FROM TIMESTAMP '2022-05-16 12:41:13.662522');

 extract
---------
       5

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

SELECT extract(QUARTER FROM TIMESTAMP '2022-05-16 12:41:13.662522');

 extract
---------
       2

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

SELECT extract(SECOND FROM TIMESTAMP '2022-05-16 12:41:13.662522');

  extract
-----------
 13.662522

You can use the extract() function to get the week of the year, as follows:

SELECT extract(WEEK FROM TIMESTAMP '2022-05-16 12:41:13.662522');

 extract
---------
      20

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

SELECT extract(YEAR FROM TIMESTAMP '2022-05-16 12:41:13.662522');

 extract
---------
    2022

extract() Syntax

Parameters

Return value

extract() Examples

On this page

`extract()` Syntax

`extract()` Examples