Oracle EXTRACT()

What is an Oracle EXTRACT() Function?

An Oracle EXTRACTFunction is used to extract components from the given input, as EXTRACT function name suggests. It extracts and returns a specified date-time value or interval value.

• It returns a string containing the appropriate time zone name or abbreviation if we extract it.
• This is a very useful function for manipulating date-time in a large table.
• Oracle 9i or later versions supports EXTRACT() function.
• It is used to extract TIMESTAMP, DATE. Interval value or XML components from the given input.
• In ambiguity case for the combinations of date-time field and interval value expression, Oracle returns UNKNOWN as a result.

Syntax

EXTRACT(Component FROM [Date_time or Interval_valueexpression])

Components are: Year, Month, Day, Hour, Minute, Second, Timezone_hour, Timezone_minute, Timezone_region, Timezone_abbr

Description

• COMPONENT: It is listed above, it is to be extracted.
• [Date_time or Interval_valueexpression]: It can be a DATE or TIMESTAMP or an INTERVAL value from which a field can be extracted using components.

Syntax to Extract XML Components

EXTRACT(xml-type-instance, 'xpath', 'namespace')

Description

• This function used to select a node or set of nodes with its leaf node. This is similar to the EXISTNODEfunction
• This function accepts two arguments.
• xml-type-instance: It is an object value
• ‘xpath’: This is the path from where function extracts nodes details. It can be an absolute or relative type.
• namespace: It is an optional parameter.

How EXTRACT() Function Works in Oracle?

As the extract function name suggests it’s a function which extracts some of the detail from the given expression or value. It accepts component and based on it, extract value from the Date_time or Interval_valueexpression.

• If component is YEAR / MONTH then expression datatype must be DATE, TIMESTAMP, TIMESTAMP WITH TIME ZONE, TIMESTAMP WITH LOCAL TIME ZONE, or INTERVAL YEAR TO MONTH.
• If a component is DAY then expression data type must be DATE, TIMESTAMP, TIMESTAMP WITH TIME ZONE, TIMESTAMP WITH LOCAL TIME ZONE, or INTERVAL DAY TO SECOND.
• If component is HOUR or MINUTE or SECOND then-expression datatype must be TIMESTAMP, TIMESTAMP WITH TIME ZONE, TIMESTAMP WITH LOCAL TIME ZONE, or INTERVAL DAY TO SECOND. In Oracle DATE data type does not store time or time field.
• If component is TIMEZONE_HOUR, TIMEZONE_MINUTE, TIMEZONE_ABBR, TIMEZONE_REGION or TIMEZONE_OFFSETthenexpression datatype must be TIMESTAMP WITH TIME ZONE or TIMESTAMP WITH LOCAL TIME ZONE.

Implementations of EXTRACT() Function with Examples

In this section, we’ll see the implementation of Oracle EXTRACT Function and its behavior. For that, we will use the below sample table (Employee&Msg) with 14 & 4records to understand the Oracle EXTRACT() function behavior.

SELECT * FROM Employee;

Output:

SELECT Dtime FROM Msg;

Output:

EXTRACT() Function

SELECT DOJ, EXTRACT(Month FROM DOJ) MONTH FROM Employee;

Output:

In the above example, EXTRACT function takes Month component and returns numeric value of the month from the given expression i.e. DOJ column from Employee table.

EXTRACT()Function with DATE Data Type

SELECT DOJ, EXTRACT(YEAR FROM DOJ) YEAR FROM Employee;

Output:

In the above example, EXTRACT function takes Year component and returns the numeric value of the Year in YYYY format from the given expression i.e. DOJ column from Employee table.

SELECT DOJ, EXTRACT(Hour FROM DOJ) HOUR FROM Employee;

Output:

The above SQL statement throws an error, WHY?

Because in the above EXTRACT function component is Hour but the expression or column is a DATE data type which does not store time information or data. So Extract function tries to extract the Hour but it couldn’t find Hour from the column data. That’s why it throws an error.

EXTRACT() Function with TIMESTAMP Data Type

In the Msg table, Dtime column is a TIMESTAMP data type which contains time details with date and TIME_ZONE.

SELECT Dtime, EXTRACT(Hour FROM DTIME) HOUR FROM Msg;

Output:

In the above example, the EXTRACT function takes the Hour component and extractsHour information in 24 Hour format from the given expression i.e. DOJ column from the Employee table.

EXTRACT() Function

It is very useful for splitting TIME or TIMESTAMP data into multiple columns as per requirement.

SELECT Dtime, EXTRACT(month from dtime) Month,
EXTRACT(day FROMDtime) day,
EXTRACT(year FROMDtime) year,
EXTRACT(Timezone_hourFROM Dtime) T_ZONE_HOUR,
EXTRACT(Timezone_abbr FROM Dtime) T_ZONE_ABB
FROM Msg;

Output:

The above EXTRACT function example shows that it’s very powerful function for extracting information from DATE, TIMESTAMP or Interval_valueexpression.

Tips:

• While using EXTRACT function, take extra care about DATE and TIMESTAMP data type expression.

Conclusion

Oracle EXTRACT() function is very useful function for extracting information from DATE, TIMESTAMP or Interval_valueexpression.This function is used to bifurcate the DATE OR TIMESTAMP data type using component like month. Date etc.

Recommended Articles

This is a guide to Oracle EXTRACT(). Here we discuss a what is Oracle EXTRACT(), with appropriate syntax and respective examples for better understanding. You can also go through our other related articles to learn more –

1. Oracle MD5
2. SQL INSTR()
3. ROLLUP in MySQL
4. Oracle Self Join