Oracle EXTRACT(datetime) Function

Oracle EXTRACT(datetime) is a built-in function that extracts and returns the value of the specified date field from a datetime or interval expression.

Oracle EXTRACT(datetime) Syntax

Here is the syntax of the Oracle EXTRACT(datetime) function:

EXTRACT(field FROM expr)

Parameters

field

Required. The date-time field to be extracted. field can be one of the following values:

  • YEAR
  • MONTH
  • DAY
  • HOUR
  • MINUTE
  • SECOND
  • TIMEZONE_HOUR
  • TIMEZONE_MINUTE
  • TIMEZONE_REGION
  • TIMEZONE_ABBR
expr

Required. A datetime or interval expression. expr can be any expression that evaluates to a datetime or interval data type compatible with the requested field:

  • If YEAR or MONTH is requested, expr must evaluate to an expression of data type DATE, TIMESTAMP, TIMESTAMP WITH TIME ZONE, TIMESTAMP WITH LOCAL TIME ZONE, or INTERVAL YEAR TO MONTH.

  • If DAY is requested, expr must evaluate to an expression of data type DATE, TIMESTAMP, TIMESTAMP WITH TIME ZONE, TIMESTAMP WITH LOCAL TIME ZONE, or INTERVAL DAY TO SECOND.

  • If HOUR, MINUTE, or SECOND is requested, expr must evaluate to an expression of data type TIMESTAMP, TIMESTAMP WITH TIME ZONE, TIMESTAMP WITH LOCAL TIME ZONE, or INTERVAL DAY TO SECOND. DATE is not valid here because Oracle Database considers it an ANSI DATE data type without time fields.

  • If TIMEZONE_HOUR, TIMEZONE_MINUTE, TIMEZONE_ABBR, TIMEZONE_REGION, or TIMEZONE_OFFSET is requested, expr must evaluate to an expression of data type TIMESTAMP WITH TIME ZONE or TIMESTAMP WITH LOCAL TIME ZONE.

Return Value

The Oracle EXTRACT(datetime) function returns the value of the specified date field from a datetime or interval expression.

EXTRACT interprets expr as an ANSI datetime data type. For example, EXTRACT does not treat DATE as the legacy Oracle DATE, but as an ANSI DATE without time elements. Therefore, you can extract only YEAR, MONTH, and DAY from a DATE value. Similarly, you can extract only TIMEZONE_HOUR and TIMEZONE_MINUTE from the TIMESTAMP WITH TIME ZONE data type.

Oracle EXTRACT(datetime) Examples

Here are some examples that demonstrate how to use the Oracle EXTRACT(datetime) function.

Basic Usage

To extract the year, month, day, hour, minute, and second separately from 2023-02-11 15:16:17, use the following statement:

SELECT
    EXTRACT(YEAR FROM TIMESTAMP '2023-02-11 15:16:17') YEAR,
    EXTRACT(MONTH FROM TIMESTAMP '2023-02-11 15:16:17') MONTH,
    EXTRACT(DAY FROM TIMESTAMP '2023-02-11 15:16:17') DAY,
    EXTRACT(HOUR FROM TIMESTAMP '2023-02-11 15:16:17') HOUR,
    EXTRACT(MINUTE FROM TIMESTAMP '2023-02-11 15:16:17') MINUTE,
    EXTRACT(SECOND FROM TIMESTAMP '2023-02-11 15:16:17') SECOND
FROM dual;

Output:

   YEAR    MONTH    DAY    HOUR    MINUTE    SECOND
_______ ________ ______ _______ _________ _________
   2023        2     11      15        16        17

Fractional Seconds

The Oracle EXTRACT(datetime) function allows you to extract the seconds value with fractional seconds, as follows:

SELECT
    EXTRACT(SECOND FROM TIMESTAMP '2023-02-11 15:16:17.238000000') SECOND
FROM dual;

Output:

   SECOND
_________
   17.238

Interval

The Oracle EXTRACT(datetime) function allows you to extract fields from an interval value:

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

Output:

   YEAR    MONTH
_______ ________
     10        2

Here is another example:

SELECT
    EXTRACT(HOUR FROM INTERVAL '11:20' HOUR TO MINUTE) HOUR,
    EXTRACT(MINUTE FROM INTERVAL '11:20' HOUR TO MINUTE) MINUTE
FROM dual;

Output:

   HOUR    MINUTE
_______ _________
     11        20

Conclusion

The Oracle EXTRACT(datetime) is a built-in function that extracts and returns the value of a specified date field from a datetime or interval expression.