September 19, 2023
Summary: The PostgreSQL EXTRACT() function retrieves a field such as a year, month, and day from a date/time value.
Table of Contents
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.
See more
PostgreSQL Tutorial: Date Functions
PostgreSQL Documentation: Date/Time Functions and Operators