The Oracle EXTRACT function is very useful when you’re doing any work with a date field or date expression. Let’s learn what it is and see some examples in this article.
Purpose of the Oracle EXTRACT Function
The EXTRACT function extracts a specific datetime value from a date or interval value.
This means you can easily get the month, year, day, or any other component of a datetime value. It’s easier than doing conversions using other functions such as TO_CHAR.
I think EXTRACT is one of the most useful Oracle SQL date functions to learn.
Note: There is another EXTRACT function for XML, but this is not covered in this article.
Syntax
The syntax for the Oracle EXTRACT function is:
EXTRACT ( date_component FROM expression )
Parameters
The parameters of the EXTRACT function are:
- date_component (mandatory): This is the part of the datetime part of the expression you wish to extract. It can be any of YEAR, MONTH, DAY, HOUR, MINUTE, SECOND, TIMEZONE_HOUR, TIMEZONE_MINUTE, TIMEZONE_REGION, or TIMEZONE_ABBR. It should not be included in quotes as they are Oracle data types.
- expression (mandatory): This is the expression that the datetime value is being retrieved from. It can be a datetime or an interval data type. It can be a column or an expression.
Some things to note about this function:
- When you extract from a datetime with a timezone value, the value that is returned is in the UTC time zone.
- If Oracle cannot determine a value from the specified expression, it will return UNKNOWN.
- You can only extract YEAR, MONTH, and DAY from a DATE value.
- You can extract TIMEZONE_HOUR and TIMEZONE_MINUTE only from TIMESTAMP WITH TIME ZONE data types.
Examples of the Oracle EXTRACT Function
Here are some examples of the EXTRACT function. I find that examples are the best way for me to learn about code, even with the explanation above.
Example 1 – Extract Year from Date
This example shows how you can get the year from a date in Oracle using EXTRACT.
SELECT SYSDATE,
EXTRACT(YEAR FROM SYSDATE) as EXTRACT_EXAMPLE
FROM dual;Result:
| SYSDATE | EXTRACT_EXAMPLE |
| 23/AUG/2022 | 2022 |
Example 2 – Extract Month from Date
This example shows how you can get the month from a date in Oracle using EXTRACT.
SELECT SYSDATE,
EXTRACT(MONTH FROM SYSDATE) as EXTRACT_EXAMPLE
FROM dual;Result:
| SYSDATE | EXTRACT_EXAMPLE |
| 23/AUG/2022 | 8 |
Example 3 – Extract Day from Date
This example shows how you can get the day from a date in Oracle using EXTRACT.
SELECT SYSDATE,
EXTRACT(DAY FROM SYSDATE) as EXTRACT_EXAMPLE
FROM dual;Result:
| SYSDATE | EXTRACT_EXAMPLE |
| 23/AUG/2022 | 23 |
Example 4 – Extract Hour from Date
This example shows what happens when you try to extract a time component from a date value.
SELECT SYSDATE,
EXTRACT(HOUR FROM SYSDATE) as EXTRACT_HOUR
FROM dual;Result:
ORA-30076: invalid extract field for extract source 30076. 00000 - "invalid extract field for extract source" *Cause: The extract source does not contain the specified extract field.
This error is shown because the SYSDATE only includes YEAR, MONTH and DAY components, and we are trying to extract the HOUR component from it.
Example 5 – Extract Hour, Minute and Second
This example shows how you can use the Oracle EXTRACT function to find the hour, minute, or second from a value.
SELECT SYSTIMESTAMP,
EXTRACT(HOUR FROM SYSTIMESTAMP) as EXTRACT_HOUR,
EXTRACT(MINUTE FROM SYSTIMESTAMP) as EXTRACT_MINUTE,
EXTRACT(SECOND FROM SYSTIMESTAMP) as EXTRACT_SECOND
FROM dual;Result:
| SYSTIMESTAMP | EXTRACT_HOUR | EXTRACT_MINUTE | EXTRACT_SECOND |
| 23/AUG/22 09:43:07.203000000 AM +11:00 | 22 | 43 | 7.203 |
The minutes and seconds are showing the same as the SYSTIMESTAMP value, but why is the HOUR different?
This is due to the time zone.
You can see that the SYSTIMESTAMP shows the time as 9AM with the timezone +11 hours.
If you remove the timezone by subtracting the 11 hours, you get to the UTC timezone. This is what the EXTRACT function does.
So, 11 hours before 9AM is 10PM the previous evening. This is reflected as an HOUR value of 22.
Example 6 – Timezone Information
This example shows how you can use EXTRACT with TIMEZONE_HOUR, TIMEZONE_MINUTE, TIMEZONE_REGION, and TIMEZONE_ABBR.
SELECT SYSTIMESTAMP,
EXTRACT(TIMEZONE_HOUR FROM SYSTIMESTAMP) as EXTRACT_TIMEZONE_HOUR,
EXTRACT(TIMEZONE_MINUTE FROM SYSTIMESTAMP) as EXTRACT_TIMEZONE_MINUTE,
EXTRACT(TIMEZONE_REGION FROM SYSTIMESTAMP) as EXTRACT_TIMEZONE_REGION,
EXTRACT(TIMEZONE_ABBR FROM SYSTIMESTAMP) as EXTRACT_TIMEZONE_ABBR
FROM dual;Result:
| SYSTIMESTAMP | EXTRACT_ TIMEZONE_ HOUR |
EXTRACT_ TIMEZONE_ MINUTE |
EXTRACT_ TIMEZONE_ REGION |
EXTRACT_ TIMEZONE_ ABBR |
| 23/AUG/22 09:43:07.203000000 AM +11:00 | 11 | 0 | UNKNOWN | UNK |
You can see that the TIMEZONE_HOUR and TIMEZONE_MINUTE reflect the timezone offset for the SYSTIMESTAMP value.
However, the TIMEZONE_REGION is set to UNKNOWN, because there are multiple regions with the same 11 hour timezone offset.
The TIMEZONE_ABBR also has the same issue, as there are multiple timezone regions with the same offset.
Example 7 – Using Interval
This example shows how you can use the Oracle EXTRACT function on an interval data type.
SELECT TO_YMINTERVAL('03-02'),
EXTRACT(YEAR FROM TO_YMINTERVAL('03-02')) as EXTRACT_YEAR,
EXTRACT(MONTH FROM TO_YMINTERVAL('03-02')) as EXTRACT_MONTH
FROM dual;Result:
| TO_YMINTERVAL(’03-02′) | EXTRACT_YEAR | EXTRACT_MONTH |
| +03-02 | 3 | 2 |
The YMINTERTVAL data type is made up of a Year and Month value, which have been extracted here.
Similar Functions
Some functions which are similar to the Oracle EXTRACT function are:
- TO_DATE – This function converts a string to a date value.
- TO_CHAR – This function converts a value to a varchar or string value.
- TRUNC – This function, when performed on dates, removes the time portion and only shows a date.
You can find a full list of Oracle SQL functions here.