The PostgreSQL EXTRACT() function retrieves a field such as year, month, day, etc., from a date / time value.
Syntax
The following illustrates the syntax of the EXTRACT() function:
1 | 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 Saturday (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:
1 | SELECT EXTRACT(YEAR FROM TIMESTAMP '2016-12-31 13:30:15'); |
Here is the result:
1 | 2016 |
Extracting the quarter from a timestamp:
1 | SELECT EXTRACT(QUARTER FROM TIMESTAMP '2016-12-31 13:30:15'); |
The result is
1 | 4 |
Extracting month from a timestamp:
1 | SELECT EXTRACT(MONTH FROM TIMESTAMP '2016-12-31 13:30:15'); |
The following is the result:
1 | 12 |
Extracting day from a timestamp:
1 | SELECT EXTRACT(DAY FROM TIMESTAMP '2016-12-31 13:30:15'); |
Here is the result:
31
Extracting century from a timestamp:
1 | SELECT EXTRACT(CENTURY FROM TIMESTAMP '2016-12-31 13:30:15'); |
It returned 21 as expected:
1 | 21 |
Extracting decade from a timestamp:
1 | SELECT EXTRACT(DECADE FROM TIMESTAMP '2016-12-31 13:30:15'); |
The following is the result:
1 | 201 |
Extracting the day of week from a timestamp:
1 | SELECT EXTRACT(DOW FROM TIMESTAMP '2016-12-31 13:30:15'); |
The result is:
1 | 6 |
Extracting the day of year from a timestamp:
1 | SELECT EXTRACT(DOY FROM TIMESTAMP '2016-12-31 13:30:15'); |
It returned 366:
1 | 366 |
Extracting the epoch from a timestamp:
1 | SELECT EXTRACT(EPOCH FROM TIMESTAMP '2016-12-31 13:30:15'); |
The result is:
1 | 1483191015 |
Extracting hour from a timestamp:
1 | SELECT EXTRACT(HOUR FROM TIMESTAMP '2016-12-31 13:30:15'); |
Result:
1 | 13 |
Extracting the minute from a timestamp:
1 | SELECT EXTRACT(MINUTE FROM TIMESTAMP '2016-12-31 13:30:15'); |
Here is the result:
1 | 30 |
Extracting second from a timestamp:
1 | SELECT EXTRACT(SECOND FROM TIMESTAMP '2016-12-31 13:30:15.45'); |
The result includes second and its fractional seconds:
1 | 15.45 |
Extracting the weekday according to ISO 8601:
1 | SELECT EXTRACT(ISODOW FROM TIMESTAMP '2016-12-31 13:30:15'); |
Extracting the millisecond from a timestamp:
1 | SELECT EXTRACT(MILLISECONDS FROM TIMESTAMP '2016-12-31 13:30:15'); |
The result is 15 * 1000 = 15000
1 | 15000 |
Extracting the microseconds from a timestamp:
1 | SELECT EXTRACT(MICROSECONDS FROM TIMESTAMP '2016-12-31 13:30:15'); |
The result is 15 * 1000000 = 15000000
1 | 15000000 |
B) Extracting from an interval examples
Extracting year from an interval:
1 | SELECT EXTRACT(YEAR FROM INTERVAL '6 years 5 months 4 days 3 hours 2 minutes 1 second' ); |
Result
1 | 4 |
Extracting the quarter from an interval:
1 | SELECT EXTRACT(QUARTER FROM INTERVAL '6 years 5 months 4 days 3 hours 2 minutes 1 second' ); |
Result
1 | 2 |
Extracting the month from an interval:
1 | SELECT EXTRACT(MONTH FROM INTERVAL '6 years 5 months 4 days 3 hours 2 minutes 1 second' ); |
Result
1 | 5 |
Extracting the day from an interval:
1 | SELECT EXTRACT(DAY FROM INTERVAL '6 years 5 months 4 days 3 hours 2 minutes 1 second' ); |
Result
1 | 4 |
Extracting the hour from an interval:
1 | SELECT EXTRACT(HOUR FROM INTERVAL '6 years 5 months 4 days 3 hours 2 minutes 1 second' ); |
Result
1 | 3 |
Extracting the minute from an interval:
1 | SELECT EXTRACT(MINUTE FROM INTERVAL '6 years 5 months 4 days 3 hours 2 minutes 1 second' ); |
Result
1 | 2 |
Extracting the second from an interval:
1 | SELECT EXTRACT(SECOND FROM INTERVAL '6 years 5 months 4 days 3 hours 2 minutes 1 second' ); |
Result
1 | 1 |
Extracting the millisecond from an interval:
1 | SELECT EXTRACT(MILLISECONDS FROM INTERVAL '6 years 5 months 4 days 3 hours 2 minutes 1 second' ); |
Result
1 | 1000 |
Extracting the microsecond from an interval:
1 | SELECT EXTRACT(MICROSECONDS FROM INTERVAL '6 years 5 months 4 days 3 hours 2 minutes 1 second' ); |
Result
1 | 1000000 |
Extracting the decade from an interval:
1 | SELECT EXTRACT(DECADE FROM INTERVAL '60 years 5 months 4 days 3 hours 2 minutes 1 second' ); |
Result
1 | 60 |
Extracting the millennium from an interval:
1 | SELECT EXTRACT(MILLENNIUM FROM INTERVAL '1999 years 5 months 4 days 3 hours 2 minutes 1 second' ); |
Result
1 | 1 |
Extracting the century from an interval:
1 | SELECT EXTRACT(CENTURY FROM INTERVAL '1999 years 5 months 4 days 3 hours 2 minutes 1 second' ); |
Result
1 | 19 |
In this tutorial, you have learned how to extract a field from a date / time or interval value.