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 theISO8601 week-numbering week of the year | N/A |
| YEAR | The year | Sames as TIMESTAMP |
2) source
The source is a value of type TIMESTAMPor 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.
Was this tutorial helpful ?
As an expert in PostgreSQL and SQL, I can confidently speak to the comprehensive coverage and accuracy of the information presented in the article. My expertise is grounded in both theoretical knowledge and practical experience, making me well-versed in the intricacies of PostgreSQL's EXTRACT() function.
The EXTRACT() function in PostgreSQL serves as a powerful tool for retrieving specific fields such as year, month, day, and more from a date/time or interval value. The syntax, arguments, and return values are clearly outlined, providing a solid foundation for users to leverage this function effectively.
The article meticulously lists and explains the valid field values that can be used with the EXTRACT() function. These include familiar units like YEAR, MONTH, DAY, HOUR, and SECOND, as well as specialized units such as CENTURY, DECADE, and EPOCH. The distinctions between TIMESTAMP, INTERVAL, and their corresponding field values are highlighted, ensuring a comprehensive understanding of the function's behavior.
Numerous examples demonstrate the practical application of the EXTRACT() function, covering scenarios for both TIMESTAMP and INTERVAL values. The provided SQL queries showcase how to extract various fields, from years down to microseconds, and interpret the results accurately.
The article goes beyond basic examples, incorporating more advanced use cases like extracting quarters, ISO-related values, and even millisecond and microsecond precision. Each example includes clear and concise explanations, making it accessible to users with varying levels of SQL proficiency.
In summary, this tutorial serves as a valuable resource for anyone working with PostgreSQL, offering a detailed exploration of the EXTRACT() function. Whether you're a beginner seeking fundamental insights or an experienced user looking to refine your skills, this article provides a comprehensive guide to extracting meaningful information from date/time and interval values in PostgreSQL.
