|Oracle® OLAP DML Reference
10g Release 1 (10.1)
Part Number B10339-02
NEW_TIME(datetime-exp this_zone new_zone)
A text expression that indicates the time zone from which you want to convert datetime-exp. It must be a valid time zone, as listed in the following table.
A text expression that indicates the time zone into which you want to convert datetime-exp. It is the time zone of the return value. It must be a valid time zone, as listed in Table 18-1, "Time Zones".
|AST||Atlantic Standard Time|
|ADT||Atlantic Daylight Time|
|BST||Bering Standard Time|
|BDT||Bering Daylight Time|
|CST||Central Standard Time|
|CDT||Central Daylight Time|
|EST||Eastern Standard Time|
|EDT||Eastern Daylight Time|
|GMT||Greenwich Mean Time|
|HST||Alaska-Hawaii Standard Time|
|HDT||Alaska-Hawaii Daylight Time|
|MST||Mountain Standard Time|
|MDT||Mountain Daylight Time|
|NST||Newfoundland Standard Time|
|PST||Pacific Standard Time|
|PDT||Pacific Daylight Time|
|YST||Yukon Standard Time|
|YDT||Yukon Daylight Time|
Example 18-22 Using the Current Time of day
The SYSDATE function returns the current date and time to the NEW_TIME function.
SHOW new_time(SYSDATE 'EST' 'PST')
When the date and time in Eastern Standard Time are October 20, 2000, at 1:20 A.M., then the date in Pacific Standard Time, which is three hours earlier, is October 19, 2000. Because SYSDATE uses the format specified by NLS_DATE_FORMAT, which by default only shows the date, the time is not displayed.
Example 18-23 Specifying the Time of day
In the following example, the TO_DATE function converts a text string to a valid date and time. The TO_CHAR function includes a date format that temporarily overrides the date format specified by the NLS_DATE_FORMAT option.
SHOW TO_CHAR(NEW_TIME(TO_DATE('11-27-00 22:15:00', 'MM-DD-YY HH24:MI:SS'), - 'HST' 'PST') 'MM-DD-YY HH24:MI:SS')
This statement converts November 27 at 10:15 P.M. (22:15:00) Alaska-Hawaii Standard Time to November 28 at 12:15 A.M. (00:15:00) Pacific Standard Time. The date format specified in the TO_CHAR function allows the time to be displayed along with the date.
Alternatively, you can change the value of NLS_DATE_FORMAT.
NLS_DATE_FORMAT = 'MM-DD-YY HH24:MI:SS'
Then this statement produces the same result, without requiring the use of TO_CHAR.
SHOW NEW_TIME(TO_DATE('11-27-00 22:15:00', 'MM-DD-YY HH24:MI:SS'), - 'HST' 'PST')