EXTRACT

 The EXTRACT function in Oracle is used to retrieve a specific part (such as the year, month, day, hour, minute, second, etc.) from a date, timestamp, or interval. It is useful when you need to isolate certain components of a date or time value for further manipulation or comparison.

 

Syntax:

EXTRACT (date_part FROM date_value)

• date_part: The part of the date or timestamp that you want to extract. This can be one of several predefined units (e.g., YEAR, MONTH, DAY, HOUR, MINUTE, SECOND).
• date_value: The date, timestamp, or interval value from which you want to extract the specified date part.

Return Type:

• The return type of the EXTRACT function is usually a numeric value, depending on the date_part you specify. For example:
• For YEAR, it returns a 4-digit number (e.g., 2025).
• For MONTH, it returns a number from 1 to 12 (e.g., 2 for February).
• For HOUR, it returns an integer from 0 to 23 (in 24-hour format).

 

Supported date_part Values:

The EXTRACT function can extract a variety of different date or time components. Some of the common date_part values include:

• YEAR: Extracts the year part of a date or timestamp.
• MONTH: Extracts the month part (1-12) of a date or timestamp.
• DAY: Extracts the day of the month (1-31) from a date or timestamp.
• HOUR: Extracts the hour part (0-23) of a timestamp or date.
• MINUTE: Extracts the minute part (0-59) of a timestamp or date.
• SECOND: Extracts the second part (0-59) of a timestamp or date.
• TIMEZONE_HOUR: Extracts the hour component of the time zone of a TIMESTAMP WITH TIME ZONE.
• TIMEZONE_MINUTE: Extracts the minute component of the time zone of a TIMESTAMP WITH TIME ZONE.
• WEEK: Extracts the week number of the year from a date.
• DOW (Day of Week): Extracts the day of the week from a date (0 for Sunday, 1 for Monday, etc.).
• ISO_WEEK: Extracts the ISO week number of the year.
• QUARTER: Extracts the quarter of the year (1 to 4).
• DAYOFYEAR: Extracts the day of the year (1 to 366).

Examples of Using EXTRACT:

1. Extract the Year from a Date:

• Example: Extract the year part from a given DATE.

SELECT EXTRACT(YEAR FROM DATE '2025-07-10') FROM dual;

-- Result: 2025

2. Extract the Month from a Date:

• Example: Extract the month part (1-12) from a given DATE.

SELECT EXTRACT(MONTH FROM DATE '2025-07-10') FROM dual;

-- Result: 7 (for July)

3. Extract the Day of the Month from a Date:

• Example: Extract the day (1-31) from a given DATE.

SELECT EXTRACT(DAY FROM DATE '2025-07-10') FROM dual;

-- Result: 10

4. Extract the Hour from a TIMESTAMP:

• Example: Extract the hour part (0-23) from a given TIMESTAMP.

SELECT EXTRACT(HOUR FROM TIMESTAMP '2025-07-10 14:30:00') FROM dual;

-- Result: 14

5. Extract the Minute from a TIMESTAMP:

• Example: Extract the minute part (0-59) from a given TIMESTAMP.

SELECT EXTRACT(MINUTE FROM TIMESTAMP '2025-07-10 14:30:00') FROM dual;

-- Result: 30

6. Extract the Second from a TIMESTAMP:

• Example: Extract the second part (0-59) from a given TIMESTAMP.

SELECT EXTRACT(SECOND FROM TIMESTAMP '2025-07-10 14:30:45') FROM dual;

-- Result: 45

7. Extract the Timezone Hour from a TIMESTAMP WITH TIME ZONE:

• Example: Extract the timezone hour from a given TIMESTAMP WITH TIME ZONE.

SELECT EXTRACT(TIMEZONE_HOUR FROM TIMESTAMP '2025-07-10 14:30:00 -07:00') FROM dual;

-- Result: -7 (The time zone offset in hours)

8. Extract the Week Number of the Year:

• Example: Extract the week number (1-52) from a given DATE.

SELECT EXTRACT(WEEK FROM DATE '2025-07-10') FROM dual;

-- Result: 28 (the week number of the year)

9. Extract the Day of the Week (DOW):

• Example: Extract the day of the week (0 for Sunday, 1 for Monday, etc.) from a DATE.

SELECT EXTRACT(DOW FROM DATE '2025-07-10') FROM dual;

-- Result: 4 (Thursday)

10. Extract the ISO Week:

• Example: Extract the ISO week number from a given DATE.

SELECT EXTRACT(ISO_WEEK FROM DATE '2025-07-10') FROM dual;

-- Result: 28 (ISO week number of the year)

 

Important Notes:

1.     Extracting from DATE vs. TIMESTAMP:

• The EXTRACT function can work with both DATE and TIMESTAMP types. If you're extracting from a TIMESTAMP, you get additional components like hour, minute, and second. For DATE, the extraction is limited to the date parts (year, month, day).

2.     Time Zone Handling:

• When working with TIMESTAMP WITH TIME ZONE, the EXTRACT function can extract parts of the time zone, such as TIMEZONE_HOUR and TIMEZONE_MINUTE.

3.     Oracle’s Date Format:

• EXTRACT works according to Oracle's internal date format, which is stored as a number (days since January 1, 4712 BC). It ensures the components are extracted as per the correct calendar rules.

4.     Default Behavior for EXTRACT:

• If you extract a YEAR, MONTH, DAY, HOUR, MINUTE, or SECOND from a DATE, you get the numeric value representing the specific part of the date or time. The result is always numeric.

5.     Extracting Parts from an Interval:

• EXTRACT can also be used with INTERVAL values (such as INTERVAL YEAR TO MONTH or INTERVAL DAY TO SECOND). For instance, you can extract the YEAR or MONTH part from an interval.

Example:

SELECT EXTRACT(YEAR FROM INTERVAL '2-6' YEAR TO MONTH) FROM dual;

-- Result: 2 (Extracts the year part from the interval '2-6')

6.     Performance Considerations:

• EXTRACT is generally fast for extracting date parts. However, depending on the complexity of the query, extracting multiple components from large datasets might affect performance, especially when working with TIMESTAMP or TIMESTAMP WITH TIME ZONE.

 

Common Use Cases:

1.     Date Component Isolation:

• Extracting specific components (like the year, month, or day) for use in business logic, reporting, or analysis.

2.     Time Calculations:

• Extracting hours, minutes, and seconds for time-based calculations, comparisons, or calculations for scheduling and events.

3.     Business Logic:

• Calculating things like fiscal year, quarter, or custom periods based on specific date components.

4.     Aggregating Data by Time Period:

• Using EXTRACT to group data by year, month, or day for reporting purposes.

 

Summary:

The EXTRACT function in Oracle is a powerful tool for extracting specific parts of a date, timestamp, or interval. By specifying the date_part (such as YEAR, MONTH, DAY, etc.), you can retrieve individual components from date and time values. This is essential for performing operations or comparisons based on specific elements of a timestamp or date, making it widely used in reporting, scheduling, and business logic applications.