PostgreSQL EXTRACT Function

The PostgreSQL EXTRACT() function retrieves a field such as a year, month, and day from a date/time value.

Syntax

The following illustrates the syntax of the EXTRACT() function:

EXTRACT(field FROM source)
Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Arguments

The PostgreSQL EXTRACT() function requires two arguments:

1) field

The field argument specifies which field to extract from the date/time value.

The following table illustrates the valid field values:

Field ValueTIMESTAMPInterval
CENTURYThe centuryThe number of centuries
DAYThe day of the month (1-31)The number of days
DECADEThe decade that is the year divided by 10Sames as TIMESTAMP
DOWThe day of week Sunday (0) to Saturday (6)N/A
DOYThe day of year that ranges from 1 to 366N/A
EPOCHThe number of seconds since 1970-01-01 00:00:00 UTCThe total number of seconds in the interval
HOURThe hour (0-23)The number of hours
ISODOWDay of week based on ISO 8601 Monday (1) to Sunday (7)N/A
ISOYEARISO 8601 week number of yearN/A
MICROSECONDSThe seconds field, including fractional parts, multiplied by 1000000Sames as TIMESTAMP
MILLENNIUMThe millenniumThe number of millennium
MILLISECONDSThe seconds field, including fractional parts, multiplied by 1000Sames as TIMESTAMP
MINUTEThe minute (0-59)The number of minutes
MONTHMonth, 1-12The number of months, modulo (0-11)
QUARTERQuarter of the yearThe number of quarters
SECONDThe secondThe number of seconds
TIMEZONEThe timezone offset from UTC, measured in secondsN/A
TIMEZONE_HOURThe hour component of the time zone offsetN/A
TIMEZONE_MINUTEThe minute component of the time zone offsetN/A
WEEKThe number of the ISO 8601 week-numbering week of the yearN/A
YEARThe yearSames as TIMESTAMP

2) source

The source is a value of type TIMESTAMP or INTERVAL. If you pass a DATE value, the function will cast it to a TIMESTAMP value.

Return value

The EXTRACT() function returns a double precision value.

Examples

A) Extracting from a TIMESTAMP examples

Extracting year from a timestamp:

SELECT EXTRACT(YEAR FROM TIMESTAMP '2016-12-31 13:30:15');
Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Here is the result:

2016
Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Extracting the quarter from a timestamp:

SELECT EXTRACT(QUARTER FROM TIMESTAMP '2016-12-31 13:30:15');
Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

The result is

4
Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Extracting month from a timestamp:

SELECT EXTRACT(MONTH FROM TIMESTAMP '2016-12-31 13:30:15');
Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

The following is the result:

12
Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Extracting day from a timestamp:

SELECT EXTRACT(DAY FROM TIMESTAMP '2016-12-31 13:30:15');
Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Here is the result:
31

Extracting century from a timestamp:

SELECT EXTRACT(CENTURY FROM TIMESTAMP '2016-12-31 13:30:15');
Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

It returned 21 as expected:

21
Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Extracting decade from a timestamp:

SELECT EXTRACT(DECADE FROM TIMESTAMP '2016-12-31 13:30:15');
Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

The following is the result:

201
Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Extracting the day of week from a timestamp:

SELECT EXTRACT(DOW FROM TIMESTAMP '2016-12-31 13:30:15');
Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

The result is:

6
Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Extracting the day of year from a timestamp:

SELECT EXTRACT(DOY FROM TIMESTAMP '2016-12-31 13:30:15');
Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

It returned 366:

366
Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Extracting the epoch from a timestamp:

SELECT EXTRACT(EPOCH FROM TIMESTAMP '2016-12-31 13:30:15');
Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

The result is:

1483191015
Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Extracting hour from a timestamp:

SELECT EXTRACT(HOUR FROM TIMESTAMP '2016-12-31 13:30:15');
Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Result:

13
Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Extracting the minute from a timestamp:

SELECT EXTRACT(MINUTE FROM TIMESTAMP '2016-12-31 13:30:15');
Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Here is the result:

30
Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Extracting second from a timestamp:

SELECT EXTRACT(SECOND FROM TIMESTAMP '2016-12-31 13:30:15.45');
Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

The result includes second and its fractional seconds:

15.45
Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Extracting the weekday according to ISO 8601:

SELECT EXTRACT(ISODOW FROM TIMESTAMP '2016-12-31 13:30:15');
Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Extracting the millisecond from a timestamp:

SELECT EXTRACT(MILLISECONDS FROM TIMESTAMP '2016-12-31 13:30:15');
Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

The result is 15 * 1000 = 15000

15000
Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Extracting the microseconds from a timestamp:

SELECT EXTRACT(MICROSECONDS FROM TIMESTAMP '2016-12-31 13:30:15');
Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

The result is 15 * 1000000 = 15000000

15000000
Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

B) Extracting from an interval examples

Extracting year from an interval:

SELECT EXTRACT(YEAR FROM INTERVAL '6 years 5 months 4 days 3 hours 2 minutes 1 second' );
Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Result

6
Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Extracting the quarter from an interval:

SELECT EXTRACT(QUARTER FROM INTERVAL '6 years 5 months 4 days 3 hours 2 minutes 1 second' );
Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Result

2
Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Extracting the month from an interval:

SELECT EXTRACT(MONTH FROM INTERVAL '6 years 5 months 4 days 3 hours 2 minutes 1 second' );
Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Result

5
Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Extracting the day from an interval:

SELECT EXTRACT(DAY FROM INTERVAL '6 years 5 months 4 days 3 hours 2 minutes 1 second' );
Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Result

4
Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Extracting the hour from an interval:

SELECT EXTRACT(HOUR FROM INTERVAL '6 years 5 months 4 days 3 hours 2 minutes 1 second' );
Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Result

3
Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Extracting the minute from an interval:

SELECT EXTRACT(MINUTE FROM INTERVAL '6 years 5 months 4 days 3 hours 2 minutes 1 second' );
Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Result

2
Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Extracting the second from an interval:

SELECT EXTRACT(SECOND FROM INTERVAL '6 years 5 months 4 days 3 hours 2 minutes 1 second' );
Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Result

1
Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Extracting the millisecond from an interval:

SELECT EXTRACT(MILLISECONDS FROM INTERVAL '6 years 5 months 4 days 3 hours 2 minutes 1 second' );
Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Result

1000
Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Extracting the microsecond from an interval:

SELECT EXTRACT(MICROSECONDS FROM INTERVAL '6 years 5 months 4 days 3 hours 2 minutes 1 second' );
Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Result

1000000
Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Extracting the decade from an interval:

SELECT EXTRACT(DECADE FROM INTERVAL '60 years 5 months 4 days 3 hours 2 minutes 1 second' );
Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Result

60
Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Extracting the millennium from an interval:

SELECT EXTRACT(MILLENNIUM FROM INTERVAL '1999 years 5 months 4 days 3 hours 2 minutes 1 second' );
Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Result

1
Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Extracting the century from an interval:

SELECT EXTRACT(CENTURY FROM INTERVAL '1999 years 5 months 4 days 3 hours 2 minutes 1 second' );
Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Result

19
Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

In this tutorial, you have learned how to extract a field from a date/time or interval value.

Was this tutorial helpful ?