The NEW_TIME Function

I don't know about you, but I am simply unable to remember the time in Anchorage when it is 3:00 PM in Chicago. Fortunately for me, PL/SQL provides the NEW_TIME function. This function converts dates (along with their time components) from one time zone to another. The specification for NEW_ TIME is:

FUNCTION NEW_TIME (date_in DATE, zone1 VARCHAR2, zone2 VARCHAR2) RETURN DATE

where date_in is the original date, zone1 is the starting point for the zone switch (usually, but not restricted to, your own local time zone), and zone2 is the time zone in which the date returned by NEW_TIME should be placed.

The NEW_TIME Function - student2.ru If you're writing software that must work across time zones, we recommend using the newer TIMESTAMP WITH TIME ZONE functionality rather than using NEW_TIME with DATE values.

The valid time zones are shown in Table 10-8.

Table 10-8. Time zone abbreviations and descriptions
Time zone abbreviation Description
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
The NEW_TIME Function - student2.ru Be aware that NEW_TIME does not accept all of the time zone abbreviations returned by V$TIMEZONE_NAMES. Use only those abbreviations listed in Table 10-8.

The specification of time zones to NEW_TIME is not case-sensitive, as the following example shows:

BEGIN DBMS_OUTPUT.PUT_LINE( TO_CHAR (NEW_TIME (TO_DATE ('09151994 12:30 AM', 'MMDDYYYY HH:MI AM'), 'CST', 'hdt'), 'Month DD, YYYY HH:MI AM'));END;

The output is:

September 14, 1994 09:30 PM

So, when it was 12:30 in the morning of September 15, 1994 in Chicago, it was 9:30 in the evening of September 14, 1994 in Anchorage.

The NEW_TIME Function - student2.ru By the way, I used TO_DATE with a format mask to make sure that a time other than the default of midnight would be used in the calculation of the new date and time. I then used TO_CHAR with another format mask (this one intended to make the output more readable) to display the date and time, because by default PL/SQL will not include the time component unless specifically requested to do so.

Even though NEW_TIME converts between time zones, it does not respect the time zones in the TIMESTAMP WITH TIME ZONE datatype. The following example demonstrates this:

DECLARE a TIMESTAMP WITH TIME ZONE := TO_TIMESTAMP_TZ ('09151994 12:30 AM -5:00', 'MMDDYYYY HH:MI AM TZH:TZM');BEGIN DBMS_OUTPUT.PUT_LINE(a); DBMS_OUTPUT.PUT_LINE( TO_CHAR (NEW_TIME (a,'CST', 'hdt'), 'Month DD, YYYY HH:MI AM'));END;

The output is:

15-SEP-94 12.30.00.000000 AM -05:00September 14, 1994 09:30 PM

Notice that the shifted time in this example is 9:30 PM, just as in the previous example. The NEW_TIME function takes a DATE as input. When the TIMESTAMP WITH TIME ZONE value is converted to a DATE, the time zone information is lost. The end result is that 12:30 AM gets shifted +9:00 hours from CST to HDT.

Наши рекомендации