Putting It Together: Time Zone Conversion Function

Oracle's built-in time zone handling makes it simple to write a function that converts a datetime from one time zone to another. Use the AT TIME ZONE syntax with another Oracle time zone function, FROM_TZ, to create this function.

FROM_TZ takes a timestamp and a time zone, and returns a timestamp with time zone. (See the article "Time Zone Quick Reference" in the Related Resources section for other useful timestamp and time zone functions.)

For example:

SQL> select from_tz(to_timestamp('01/01/2006 23:45','mm/dd/yyyy hh24:mi'),'-05:00') from dual;

FROM_TZ(TO_TIMESTAMP('01/01/200623:45','MM/DD/YYYYHH24:MI'),'-05:00')
---------------------------------------------------------------------------
01/01/2006 23:45:00.000000000 -05:00

1 row selected.

So, to create a function that converts a timestamp from one time zone to another, first put the timestamp in the source time zone with FROM_TZ, and then convert it to the target time zone with AT TIME ZONE:

create or replace
function convert_time ( datetime in timestamp, tz1 in varchar2, tz2 in varchar2 )
return timestamp with time zone
as
	retval timestamp with time zone;
begin	

	retval := from_tz(datetime, tz1) at time zone tz2;
	return retval;
end;

To try this out, call the function to convert "23:45 on January 01, 2006" from New York's time zone (-05:00) to Istanbul's time zone (+02:00):

SQL> select convert_time(to_timestamp('01/01/2006 23:45','mm/dd/yyyy hh24:mi'),'-05:00','+02:00') from dual ;

CONVERT_TIME(TO_TIMESTAMP('01/01/200623:45','MM/DD/YYYYHH24:MI'),'-05:00','
---------------------------------------------------------------------------
01/02/2006 06:45:00.000000000 +02:00

1 row selected.

You can use time zone names (see Sidebar 3. Time Zone Names for details) instead of supplying the time-zone displacement:

SQL> select convert_time(to_timestamp('01/01/2006 23:45','mm/dd/yyyy hh24:mi'), 'US/Eastern', 'Turkey')       
  2  from dual;

CONVERT_TIME(TO_TIMESTAMP('01/01/200623:45','MM/DD/YYYYHH24:MI'),'US/EASTER
---------------------------------------------------------------------------
01/02/2006 06:45:00.000000000 +02:00

1 row selected.

Daylight Savings Time

For most of the year, it doesn't matter whether you work with time zones by specifying the displacement (e.g., '-05:00') or the name (e.g., 'US/Eastern'). But, if you operate in a region that uses Daylight Savings Time (DST), twice a year it does matter very much. If you specify time zones by name, then Oracle will automatically manage any necessary time zone-displacement DST changes for you.

For example, the US/Eastern time zone runs on Daylight Savings Time from 2:00 AM EST on the first Sunday of April until 2:00 AM EDT on the last Sunday of October. During Daylight Savings Time, the time zone displacement in the US/Eastern time zone is -04:00; when DST is not in effect, the displacement is -05:00.

If you specify time zones by using the displacement, or by using abbreviations like EST or EDT, then any operations on timestamps using these time zones will return results that use the original displacement. But any operations on timestamps using named time zones return results that are correct for the named time zone, even if the operation crosses a DST boundary.

An example will make this clearer. In 2006, in the US/Eastern time zone, the switch to DST occurs at 2:00 AM EST on April 2. To see how Oracle handles this, add a few hours to a time just before the switch. Recall that timestamp arithmetic uses INTERVAL data types. Use the INTERVAL DAY TO SECOND data type to add a few hours to a TIMESTAMP WITH TIME ZONE datetime value.

First, add an hour and a few minutes to a TIMESTAMP WITH TIME ZONE that specifies the time zone via time-zone displacement:

SQL> select to_timestamp_tz('04/02/2006 01:00:00 -05:00','mm/dd/yyyy hh24:mi:ss tzh:tzm')  
  2  + to_dsinterval('0 01:13:23')
  3  from dual;

TO_TIMESTAMP_TZ('04/02/200601:00:00+05:00','MM/DD/YYYYHH24:MI:SSTZH:TZM')+T
---------------------------------------------------------------------------
04/02/2006 02:13:23.000000000 -05:00

1 row selected.

This value is correct but useless if you want to know what the time will be in, for example, New York City. New York City won't even have a 2:13 AM on April 2, 2006. It will pass straight from 2:00 AM to 3:01 AM.

Now try it with a TIMESTAMP WITH TIME ZONE that specifies the time zone via a time zone region name:

SQL> select to_timestamp_tz('04/02/2006 01:00:00 US/Eastern', 'mm/dd/yyyy hh24:mi:ss tzr')
  2  + to_dsinterval('0 01:13:23')
  3* from dual
SQL> /

TO_TIMESTAMP_TZ('04/02/200601:00:00US/EASTERN','MM/DD/YYYYHH24:MI:SSTZR')+T
---------------------------------------------------------------------------
04/02/2006 03:13:23.000000000 -04:00

1 row selected.

What happens if you try to specify a time that doesn't exist in a given time zone region? For example, as previously noted, New York City will go straight from 2:00 AM to 3:01 AM on April 2, 2006. So the expression "April 2nd, 2006 at 2:13 AM in New York City" is nonsense. Try specifying 2:13 AM in US/Eastern and see what happens:

SQL> select to_timestamp_tz('04/02/2006 02:13:00 US/Eastern', 'mm/dd/yyyy hh24:mi:ss tzr') from dual ;
select to_timestamp_tz('04/02/2006 02:13:00 US/Eastern', 'mm/dd/yyyy hh24:mi:ss tzr') from dual
                       *
ERROR at line 1:
ORA-01878: specified field not found in datetime or interval

What about the period at the end of DST when clocks are set back from 2:00 AM to 1:00 AM? The expression "October 29th, 2006 at 1:01 AM in New York City" actually refers to two different times an hour apart: 1:01 AM EDT, before DST ends at 2 AM, and 1:01 AM EST, after the clocks have been set back.

How Oracle handles this depends on the value of a session-level variable, ERROR_ON_OVERLAP_TIME. The default value is FALSE. When ERROR_ON_OVERLAP_TIME is set to FALSE, Oracle assumes that an ambiguous time expression like "October 29th, 2006 at 1:01 AM in New York City" refers to the standard, non-daylight, time. Thus, "October 29th, 2006 at 1:01 AM in New York City" is taken to refer to 1:01 AM EST, after the clocks have been set back. When ERROR_ON_OVERLAP_TIME is set to TRUE, Oracle doesn't evaluate an ambiguous time expression; it returns an error.

Test this by entering "October 29th, 2006 at 1:01 AM in New York City" as a TIMESTAMP WITH TIME ZONE. First, try it without setting ERROR_ON_OVERLAP_TIME, which will leave it at its default value of FALSE:

SQL> select to_timestamp_tz('10/29/2006 01:01:00 US/Eastern', 'mm/dd/yyyy hh24:mi:ss tzr') from dual ;

TO_TIMESTAMP_TZ('10/29/200601:01:00US/EASTERN','MM/DD/YYYYHH24:MI:SSTZR')
---------------------------------------------------------------------------
10/29/2006 01:01:00.000000000 -05:00

1 row selected.

Oracle interpreted '10/29/2006 01:01:00 US/Eastern' to refer to '10/29/2006 01:01:00 -05:00' (i.e., with the Eastern Standard Time offset of -05:00). Now, set ERROR_ON_OVERLAP_TIME to true and try it again:

SQL> alter session set ERROR_ON_OVERLAP_TIME=true ;

Session altered.

SQL> select to_timestamp_tz('10/29/2006 01:01:00 US/Eastern', 'mm/dd/yyyy hh24:mi:ss tzr') from dual ;
select to_timestamp_tz('10/29/2006 01:01:00 US/Eastern', 'mm/dd/yyyy hh24:mi:ss tzr') from dual
                       *
ERROR at line 1:
ORA-01883: overlap was disabled during a region transition

So, as a developer, you have some control over how Oracle interprets ambiguous time expressions like "October 29th, 2006 at 1:01 AM in New York City".