Introduction to PostgreSQL EXTRACT() function

TheEXTRACT() function extracts a field from a date/time value. Here’s the basic syntax of theEXTRACT() function:

EXTRACT(fieldFROM source)

The PostgreSQLEXTRACT() function requires two arguments:

1)field

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

The following table illustrates the valid field values:

2)source

Thesource is a value of typeTIMESTAMP orINTERVAL. If you pass aDATE value, the function will cast it to aTIMESTAMP value.

TheEXTRACT() function returns a double precision value.

PostgreSQL EXTRACT function examples

Let’s explore some examples of using theEXTRACT() function.

1) Basic PostgreSQL EXTRACT() function examples

The following example uses theEXTRACT() function to extract the year from a timestamp:

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

Output:

y------ 2016(1 row)

The following example uses theEXTRACT() function to extract the quarter from a timestamp:

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

Output:

q--- 4(1 row)

The following example uses theEXTRACT() function to extract the month from a timestamp:

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

Output:

m---- 12(1 row)

The following example uses theEXTRACT() function to extract the day from a timestamp:

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

Output:

d---- 31(1 row)

The following example uses theEXTRACT() function to extract the century from a timestamp:

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

Output:

century---------      21(1 row)

The following example uses theEXTRACT() function to extract the decade from a timestamp:

SELECT EXTRACT(DECADEFROM TIMESTAMP '2016-12-31 13:30:15') decade;

The following is the result:

decade--------    201(1 row)

The following example uses theEXTRACT() function to extract the day of the week from a timestamp:

SELECT EXTRACT(DOWFROM TIMESTAMP '2016-12-31 13:30:15') day_of_week;

The result is:

day_of_week-------------           6(1 row)

The following example uses theEXTRACT() function to extract the day of the year from a timestamp:

SELECT EXTRACT(DOYFROM TIMESTAMP '2016-12-31 13:30:15') day_of_year;

It returned 366:

day_of_year-------------         366(1 row)

The following example uses theEXTRACT() function to extract the epoch from a timestamp:

SELECT EXTRACT(EPOCHFROM TIMESTAMP '2016-12-31 13:30:15') epoch;

The result is:

epoch------------------- 1483191015.000000(1 row)

The following example uses theEXTRACT() function to extract the hour from a timestamp:

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

Result:

h---- 13(1 row)

The following example uses theEXTRACT() function to extract the minute from a timestamp:

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

Here is the result:

min-----  30(1 row)

The following example uses theEXTRACT() function to extract the second from a timestamp:

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

The result includes second and its fractional seconds:

sec----------- 15.000000(1 row)

The following example uses theEXTRACT() function to extract the weekday according to ISO 8601:

SELECT EXTRACT(ISODOWFROM TIMESTAMP '2016-12-31 13:30:15') weekday_iso;

Output:

weekday_iso-------------           6(1 row)

The following example uses theEXTRACT() function to extract the millisecond from a timestamp:

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

The result is 15 * 1000 = 15000

ms----------- 15000.000(1 row)

The following example uses theEXTRACT() function to extract the microseconds from a timestamp:

SELECT EXTRACT(MICROSECONDSFROM TIMESTAMP '2016-12-31 13:30:15') microsec;

The result is 15 * 1000000 = 15000000

microsec---------- 15000000(1 row)

2) Extracting from an interval examples

The following example uses theEXTRACT() function to extract the year from an interval:

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

Result:

y--- 6(1 row)

The following example uses theEXTRACT() function to extract the quarter from an interval:

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

Result:

2

The following example uses theEXTRACT() function to extract the month from an interval:

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

Result:

5

The following example uses theEXTRACT() function to extract the day from an interval:

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

Result:

4

The following example uses theEXTRACT() function to extract the hour from an interval:

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

Result:

3

The following example uses theEXTRACT() function to extract the minute from an interval:

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

Result:

2

The following example uses theEXTRACT() function to extract the second from an interval:

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

Result:

1

The following example uses theEXTRACT() function to extract the millisecond from an interval:

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

Result:

1000

The following example uses theEXTRACT() function to extract the microsecond from an interval:

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

Result:

1000000

The following example uses theEXTRACT() function to extract the decade from an interval:

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

Result:

60

The following example uses theEXTRACT() function to extract the millennium from an interval:

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

Result:

1

The following example uses theEXTRACT() function to extract the century from an interval:

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

Result:

19

Summary

• Use the PostgreSQLEXTRACT() function to extract a field from a date/time or interval value.

Last updated onMarch 22, 2024

On this page

• Introduction to PostgreSQL EXTRACT() function
• PostgreSQL EXTRACT function examples
• Summary