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 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');Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)Here is the result:
2016Code 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
4Code 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:
12Code 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:
21Code 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:
201Code 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:
6Code 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:
366Code 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:
1483191015Code 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:
13Code 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:
30Code 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.45Code 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
15000Code 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
15000000Code 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
6Code 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
2Code 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
5Code 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
4Code 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
3Code 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
2Code 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
1Code 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
1000Code 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
1000000Code 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
60Code 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
1Code 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
19Code 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.
