Skip Headers
Oracle® OLAP DML Reference
11g Release 2 (11.2)

E17122-08
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
PDF · Mobi · ePub

EXTRACT

The EXTRACT function extracts and returns the value of a specified datetime value from a datetime or interval value expression. This function can be very useful for manipulating datetime values in very large variables.

Return Values

The value returned varies:

  • When extracting from a datetime with a time zone value, the function returns a value in UTC.

  • When you extract a TIMEZONE_REGION or TIMEZONE_ABBR (abbreviation), the function returns a text string that is the appropriate time zone name or abbreviation.

  • When you extract any of the other values, the function returns a value in the Gregorian calendar.

  • When the values you specify results in an ambiguity, the function returns NA.

Syntax

EXTRACT(time |timezone_hour_or_nimute |timezone_regn_or_abbr FROM datetime_exp| interval_exp )

Parameters

time

One of the following keywords: YEAR, MONTH, DAY, HOUR, MINUTE, or SECOND which specify the portion of the time that you want the function to return.

timezone_hour_or_minute

One of the following keywords: TIMEZONE_HOUR or TIMEZONE_MINUTE which specify that you want the function to return either the hour or minute portion of a TIMESTAMP_TZ expression.

timezone_regn_or_abbr

One of the following keywords: TIMEZONE_REGION or TIMEZONE_ABBR which specify that you want the function to return a string that is either the region name or its abbreviation.

datetime_exp

A DATETIME, TIMESTAMP, TIMESTAMP_TZ, or TIMESTAMP_LTZ expression. See "Datetime Expressions" for information on how to specify these expressions.

interval_exp

A DSINTERVAL or YMINTERVAL expression. See "Interval Expressions" for information on how to specify these expressions.

Usage Notes

The value you are extracting must be a value of the appropriate datetime_exp or interval_exp. For example, you can extract only YEAR, MONTH, and DAY from a DATETIME value. Likewise, you can extract TIMEZONE_HOUR and TIMEZONE_MINUTE only from the TIMESTAMP_TZ data type.

Examples

Example 7-86 Extracting the Hour from a Timestamp

DEFINE mytimestamptz VARIABLE TIMESTAMP_TZ
REPORT mytimestamptz
 
MYTIMESTAMPTZ
------------------------------
  26-MAR-06 12.00.00 AM -04:00

SHOW EXTRACT (TIMEZONE_HOUR FROM mytimestamptz)
-4.00