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.

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

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.

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.