PostgreSQL Tutorial: EXTRACT Function

September 19, 2023

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)

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 Value TIMESTAMP Interval
CENTURY The century The number of centuries
DAY The day of the month (1-31) The number of days
DECADE The decade that is the year divided by 10 Sames as TIMESTAMP
DOW The day of week Sunday (0) to Saturday (6) N/A
DOY The day of year that ranges from 1 to 366 N/A
EPOCH The number of seconds since 1970-01-01 00:00:00 UTC The total number of seconds in the interval
HOUR The hour (0-23) The number of hours
ISODOW Day of week based on ISO 8601 Monday (1) to Sunday (7) N/A
ISOYEAR ISO 8601 week number of year N/A
MICROSECONDS The seconds field, including fractional parts, multiplied by 1000000 Sames as TIMESTAMP
MILLENNIUM The millennium The number of millennium
MILLISECONDS The seconds field, including fractional parts, multiplied by 1000 Sames as TIMESTAMP
MINUTE The minute (0-59) The number of minutes
MONTH Month, 1-12 The number of months, modulo (0-11)
QUARTER Quarter of the year The number of quarters
SECOND The second The number of seconds
TIMEZONE The timezone offset from UTC, measured in seconds N/A
TIMEZONE_HOUR The hour component of the time zone offset N/A
TIMEZONE_MINUTE The minute component of the time zone offset N/A
WEEK The number of the ISO 8601 week-numbering week of the year N/A
YEAR The year Sames 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');

Here is the result:

2016

Extracting the quarter from a timestamp:

SELECT EXTRACT(QUARTER FROM TIMESTAMP '2016-12-31 13:30:15');

The result is

4

Extracting month from a timestamp:

SELECT EXTRACT(MONTH FROM TIMESTAMP '2016-12-31 13:30:15');

The following is the result:

12

Extracting day from a timestamp:

SELECT EXTRACT(DAY FROM TIMESTAMP '2016-12-31 13:30:15');

Here is the result: 31

Extracting century from a timestamp:

SELECT EXTRACT(CENTURY FROM TIMESTAMP '2016-12-31 13:30:15');

It returned 21 as expected:

21

Extracting decade from a timestamp:

SELECT EXTRACT(DECADE FROM TIMESTAMP '2016-12-31 13:30:15');

The following is the result:

201

Extracting the day of week from a timestamp:

SELECT EXTRACT(DOW FROM TIMESTAMP '2016-12-31 13:30:15');

The result is:

6

Extracting the day of year from a timestamp:

SELECT EXTRACT(DOY FROM TIMESTAMP '2016-12-31 13:30:15');

It returned 366:

366

Extracting the epoch from a timestamp:

SELECT EXTRACT(EPOCH FROM TIMESTAMP '2016-12-31 13:30:15');

The result is:

1483191015

Extracting hour from a timestamp:

SELECT EXTRACT(HOUR FROM TIMESTAMP '2016-12-31 13:30:15');

Result:

13

Extracting the minute from a timestamp:

SELECT EXTRACT(MINUTE FROM TIMESTAMP '2016-12-31 13:30:15');

Here is the result:

30

Extracting second from a timestamp:

SELECT EXTRACT(SECOND FROM TIMESTAMP '2016-12-31 13:30:15.45');

The result includes second and its fractional seconds:

15.45

Extracting the weekday according to ISO 8601:

SELECT EXTRACT(ISODOW FROM TIMESTAMP '2016-12-31 13:30:15');

Extracting the millisecond from a timestamp:

SELECT EXTRACT(MILLISECONDS FROM TIMESTAMP '2016-12-31 13:30:15');

The result is 15 * 1000 = 15000

15000

Extracting the microseconds from a timestamp:

SELECT EXTRACT(MICROSECONDS FROM TIMESTAMP '2016-12-31 13:30:15');

The result is 15 * 1000000 = 15000000

15000000

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' );

Result

6

Extracting the quarter from an interval:

SELECT EXTRACT(QUARTER FROM INTERVAL '6 years 5 months 4 days 3 hours 2 minutes 1 second' );

Result

2

Extracting the month from an interval:

SELECT EXTRACT(MONTH FROM INTERVAL '6 years 5 months 4 days 3 hours 2 minutes 1 second' );

Result

5

Extracting the day from an interval:

SELECT EXTRACT(DAY FROM INTERVAL '6 years 5 months 4 days 3 hours 2 minutes 1 second' );

Result

4

Extracting the hour from an interval:

SELECT EXTRACT(HOUR FROM INTERVAL '6 years 5 months 4 days 3 hours 2 minutes 1 second' );

Result

3

Extracting the minute from an interval:

SELECT EXTRACT(MINUTE FROM INTERVAL '6 years 5 months 4 days 3 hours 2 minutes 1 second' );

Result

2

Extracting the second from an interval:

SELECT EXTRACT(SECOND FROM INTERVAL '6 years 5 months 4 days 3 hours 2 minutes 1 second' );

Result

1

Extracting the millisecond from an interval:

SELECT EXTRACT(MILLISECONDS FROM INTERVAL '6 years 5 months 4 days 3 hours 2 minutes 1 second' );

Result

1000

Extracting the microsecond from an interval:

SELECT EXTRACT(MICROSECONDS FROM INTERVAL '6 years 5 months 4 days 3 hours 2 minutes 1 second' );

Result

1000000

Extracting the decade from an interval:

SELECT EXTRACT(DECADE FROM INTERVAL '60 years 5 months 4 days 3 hours 2 minutes 1 second' );

Result

60

Extracting the millennium from an interval:

SELECT EXTRACT(MILLENNIUM FROM INTERVAL '1999 years 5 months 4 days 3 hours 2 minutes 1 second' );

Result

1

Extracting the century from an interval:

SELECT EXTRACT(CENTURY FROM INTERVAL '1999 years 5 months 4 days 3 hours 2 minutes 1 second' ); 

Result

19

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