Oracle® OLAP DML Reference 11g Release 2 (11.2) E17122-08 |
|
|
PDF · Mobi · ePub |
The NEW_TIME function converts a date and time from one time zone to another.
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 8-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 8-33 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.
19-OCT-00
Example 8-34 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.
11-28-00 00:15:00
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')