Oracle® OLAP DML Reference 11g Release 2 (11.2) Part Number E17122-07 |
|
|
PDF · Mobi · ePub |
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.
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 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.
EXTRACT(time |timezone_hour_or_nimute |timezone_regn_or_abbr FROM datetime_exp| interval_exp )
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.
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.
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.
A DATETIME
, TIMESTAMP
, TIMESTAMP_TZ
, or TIMESTAMP_LTZ
expression. See "Datetime Expressions" for information on how to specify these expressions.
A DSINTERVAL
or YMINTERVAL
expression. See "Interval Expressions" for information on how to specify these expressions.
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.