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