What is the EXTRACT Function in Oracle?

EXTRACT Function

To extract particular elements from a date, timestamp, or interval value like the year, month, day, hour, minute, or second use Oracle SQL’s EXTRACT function. For granular manipulation and querying of datetime data, it is especially helpful.

Purpose and Functionality

You can separate and extract a certain portion of a datetime or interval value as a character or numeric representation using the EXTRACT function. Calculations based on certain time units, filtering records by year, and grouping data by month can all benefit greatly from this.

The Gregorian calendar is used by default when you extract values other than TIMEZONE_REGION or TIMEZONE_ABBR. The value that is retrieved from a datetime value that has a time zone will be in Coordinated Universal Time (UTC).

Syntax

The general syntax for the EXTRACT function is as follows:

EXTRACT (
  { YEAR | MONTH | DAY | HOUR | MINUTE | SECOND }
  | { TIMEZONE_HOUR | TIMEZONE_MINUTE }
  | { TIMEZONE_REGION | TIMEZONE_ABBR }
FROM { datetime_value_expression | interval_value_expression }
)

This is where:

1. component_name: Specifies the part of the datetime or interval you want to extract, such as YEAR, MONTH, DAY, HOUR, MINUTE, SECOND, TIMEZONE_HOUR, TIMEZONE_MINUTE, TIMEZONE_REGION, or TIMEZONE_ABBR.
2. datetime_value_expression: This can be a DATE, TIMESTAMP, TIMESTAMP WITH TIME ZONE, or TIMESTAMP WITH LOCAL TIME ZONE column or literal.
3. interval_value_expression: This can be an INTERVAL YEAR TO MONTH or INTERVAL DAY TO SECOND column or literal.

Most datetime components (such as year, month, day, hour, minute, and second) are returned as NUMBER by the function. TIMEZONE_REGION and TIMEZONE_ABBR, on the other hand, yield a VARCHAR2 string upon extraction.

Extracting Components from Dates and Timestamps

Extracting Year, Month, and Day from a

You may retrieve YEAR, MONTH, and DAY straight from a DATE datatype.

Example: To extract the year, month, and day from a specified date:

SELECT
  EXTRACT(YEAR FROM DATE '2016-07-25') AS "YEAR",
  EXTRACT(MONTH FROM DATE '2016-07-25') AS "MONTH",
  EXTRACT(DAY FROM DATE '2016-07-25') AS "DAY"
FROM DUAL;

Output:

      YEAR      MONTH        DAY
---------- ---------- ----------
      2016          7         25

Extracting Hour, Minute, and Second from a

To extract an hour, minute, or second from a DATE value, you frequently need to CAST the DATE to a TIMESTAMP first, even though the DATE datatype maintains time components down to the second.

Example: To extract time components from a DATE literal:

SELECT
  EXTRACT(HOUR FROM CAST(TO_DATE('2016-01-01 09:42:01', 'YYYY-MM-DD HH24:MI:SS') AS TIMESTAMP)) AS "Hours",
  EXTRACT(MINUTE FROM CAST(TO_DATE('2016-01-01 09:42:01', 'YYYY-MM-DD HH24:MI:SS') AS TIMESTAMP)) AS "Minutes",
  EXTRACT(SECOND FROM CAST(TO_DATE('2016-01-01 09:42:01', 'YYYY-MM-DD HH24:MI:SS') AS TIMESTAMP)) AS "Seconds"
FROM DUAL;

Output:

     HOURS    MINUTES    SECONDS
---------- ---------- ----------
         9         42          1

Extracting Components from a

Extracting all datetime fields, including fractional seconds, is automatically supported by the TIMESTAMP datatype.

Example: To extract components from a SYSTIMESTAMP value (which returns TIMESTAMP WITH TIME ZONE):

SELECT
  EXTRACT(YEAR FROM SYSTIMESTAMP) AS "Current Year",
  EXTRACT(MONTH FROM SYSTIMESTAMP) AS "Current Month",
  EXTRACT(DAY FROM SYSTIMESTAMP) AS "Current Day",
  EXTRACT(HOUR FROM SYSTIMESTAMP) AS "Current Hour",
  EXTRACT(MINUTE FROM SYSTIMESTAMP) AS "Current Minute",
  EXTRACT(SECOND FROM SYSTIMESTAMP) AS "Current Second",
  EXTRACT(TIMEZONE_HOUR FROM SYSTIMESTAMP) AS "TZ Hour Offset"
FROM DUAL;

Output (example, exact values depend on execution time and system time zone):

CURRENT_YEAR CURRENT_MONTH CURRENT_DAY CURRENT_HOUR CURRENT_MINUTE CURRENT_SECOND TZ_HOUR_OFFSET
------------ ------------- ----------- ------------ -------------- -------------- --------------
        2023             1           1           15             30

Extracting Time Zone Information

TIMEZONE_HOUR, TIMEZONE_MINUTE, TIMEZONE_REGION, and TIMEZONE_ABBR are examples of time zone-specific components that can be extracted. Keep in mind that if you supply a numerical offset in a TIMESTAMP literal, it may cause ambiguity for TIMEZONE_REGION, which would result in EXTRACT returning UNKNOWN.

Example: To extract the time zone region from a TIMESTAMP WITH TIME ZONE literal:

SELECT EXTRACT(TIMEZONE_REGION FROM TIMESTAMP '1999-10-29 01:30:00 US/Pacific PDT') AS "Region" FROM DUAL;

Output:

REGION
--------------------------------------------------------------------------------
US/PACIFIC

EXTRACT Function Using with Intervals

Using the EXTRACT function to extract particular components (such as years, months, days, hours, minutes, and seconds) from an interval value is very helpful when working with INTERVAL datatypes. Due to the lack of format models for interval datatypes, this is frequently used in conjunction with string concatenation to provide more readable output.

Example: To display the length of service in a more human-readable format from an INTERVAL YEAR TO MONTH:

DECLARE
  service_interval INTERVAL YEAR TO MONTH := INTERVAL '7-3' YEAR TO MONTH;
BEGIN
  DBMS_OUTPUT.PUT_LINE(
    EXTRACT(YEAR FROM service_interval) || ' Years and ' ||
    EXTRACT(MONTH FROM service_interval) || ' Months'
  );
END;
/

Output:

7 Years and 3 Months

Comparison

When only certain parts of date values need to be selected, like the month or day, EXTRACT can frequently be used in place of TO_CHAR. For instance, the month number can be obtained by substituting TO_CHAR(BirthDate,'MM') with EXTRACT(Month from BirthDate). But for more intricate or personalised formatting involving text or certain display patterns (such “May, 20th, 1949”), TO_CHAR is still the better option.

Benefits of Using EXTRACT Function

There are various benefits to using the EXTRACT function.

• Granular Data Manipulation: It offers exact control over the extraction of particular time and date units.
• Program Flow Control: IF EXTRACT (MONTH FROM SYSDATE) = 11 THEN…) is an example of a conditional statement that uses extracted numeric values to modify program logic.
• Simplified Calculations: It can make time unit calculations easier, particularly when interval datatypes are being used.
• SQL Standard Compliance: Because EXTRACT is a component of the SQL standard, programs may be more portable.

EXTRACT essentially functions as a specialised magnifying glass for your date and time data, enabling you to zoom in and select the specific information you require, be it the year, month, day, or exact time components.