n our increasingly interconnected world, it’s more and more important for code to be able to handle time zones gracefully. For example, if call centers in Bangalore and London use the same call center application, then the code that writes call logs to the database will have to be time zone aware?a call time recorded simply as “7:15 AM” could have happened at 7:15 AM in either London time or in Bangalore time.

Until Oracle 9i, Oracle provided a single data type for dates and times, the DATE data type, and didn’t offer any special support for time zones. In Oracle 9i, Oracle provides built-in time zone and Daylight Savings Time support. Instead of trying to stuff time zone functionality into the DATE data type, Oracle introduced TIMESTAMP data types that are time zone aware.

This article explains Oracle’s handling of time zones and shows you how to make your code time zone aware.

Introduction to the DATE and TIMESTAMP Data Types

DATEs, the datetime data type from Oracle’s earliest releases, store year, month, day, hour, and minute information. DATEs do not store fractional seconds; they offer only seconds-level precision. TIMESTAMPs store all the information DATEs can; they can also store fractional seconds, with a precision of up to 0.00000001 (10^-9) of a second.

You can set the precision of a TIMESTAMP type variable in your code with the following syntax:

TIMESTAMP[(precision)]

The default precision is 6 (i.e., TIMESTAMP(6), which has a precision of 0.000001 (10^-6) seconds).

DATE arithmetic and TIMESTAMP arithmetic have significant differences. If you subtract one date from another, the result is a NUMBER data type specifying the number of days between the two dates. For example, in DATE arithmetic, to_date(‘1/12/2005 13:00’) – to_date(‘1/12/05 12:00’) = 0.041666…, or 1/24 of a day. But in TIMESTAMP arithmetic, the interval between two timestamps is expressed in a new INTERVAL data type. INTERVALS display the year, month, day, hour, minute, second, and fractional-seconds difference between two timestamps. Performing the calculation above on TIMESTAMPs instead of DATEs would give you an INTERVAL of +000000000 01:00:00.000000 instead of .041666. This much more clearly indicates that there is one hour’s difference between the two TIMESTAMPs.

Finally, Oracle provides special TIMESTAMP data types that, unlike DATEs, offer time zone support. From 9i on, Oracle has provided time zone support within the database, and you can write your code so that it’s time zone aware by using these special TIMESTAMP data types. The following are the four datetime data types in Oracle:

• DATE
• TIMESTAMP
• TIMESTAMP WITH TIME ZONE
• TIMESTAMP WITH LOCAL TIME ZONE

The TIMESTAMP WITH TIME ZONE data types and TIMESTAMP WITH LOCAL TIME ZONE data types are time zone aware (See Sidebar 1. About Time Zone Displacement). Use these data types in your code to take advantage of Oracle’s time zone and daylight savings time functionality.

The Two Time-Zoned Data Types

Oracle provides two distinct time zone aware data types, TIMESTAMP WITH TIME ZONE and TIMESTAMP WITH LOCAL TIME ZONE. From the names, you might think they’re almost the same, but they’re actually stored and processed quite differently.

When you use a TIMESTAMP WITH TIME ZONE data type, time zone information is stored in the database along with the month, day, year, hours, minutes, seconds, and fractional seconds. Whenever you retrieve that data, the time zone that it was stored with comes along. If you don’t specify time zone information when you store the data, then the Oracle client pulls the information from your session’s time zone (See Sidebar 2. Displaying Time Zone Information).

In contrast, when you use a TIMESTAMP WITH LOCAL TIME ZONE data type, Oracle does not store any time zone information. Instead, it converts the timestamp to a “baseline” time zone that it uses to store all the TIMESTAMP WITH LOCAL TIME ZONE data in the database. When you retrieve TIMESTAMP WITH LOCAL TIME ZONE data from the database, Oracle converts it on the fly from the “baseline” time zone to the time zone of your current session.

This “baseline” time zone is what Oracle refers to as the database time zone. This “database time zone” has absolutely nothing to do with the time zone the database is actually in. It is used only for storing TIMESTAMP WITH LOCAL TIME ZONE data. When you run the SYSTIMESTAMP function, for example, you get back a TIMESTAMP WITH TIME ZONE that is in the time zone of the database server, not in the “database time zone”. (See Sidebar 3. Time Zone Names for more details.)

In fact, Oracle recommends that you set the “database time zone” to +00:00, the default, in order to speed up the on-the-fly conversions that it does with TIMESTAMP WITH LOCAL TIME ZONE data.

Explore the difference between TIMESTAMP, TIMESTAMP WITH TIME ZONE, and TIMESTAMP WITH LOCAL TIME ZONE data by going through the following example.

Start by creating a table that has fields with all three data types:

SQL> create table tmp_tst_tz  2  (ts timestamp,  3  tstz timestamp with time zone,  4  tsltz timestamp with local time zone);Table created.

Assume that your database server’s actual time zone is UTC -05:00. Then SYSTIMESTAMP will return the current time on the database server, with a time-zone displacement of -05:00 (see Sidebar 1). Start out by setting your client session’s time zone (see Sidebar 2) to match:

SQL> alter session set time_zone='-05:00';Session altered.

Now insert SYSTIMESTAMP into all three columns in the test table (see Sidebar 2 for more information on SYSTIMESTAMP):

SQL> insert into tmp_tst_tz values (systimestamp, systimestamp, systimestamp) ;1 row created.SQL> commit;Commit complete.

Before querying the table, make sure your default timestamp formats are set, so that the data will be easy to read (see Sidebar 2 for more information):

SQL> alter session set NLS_TIMESTAMP_TZ_FORMAT='mm/dd/yyyy hh24:mi:ssxff TZH:TZM' ;Session altered.SQL> alter session set NLS_TIMESTAMP_FORMAT='mm/dd/yyyy hh24:mi:ssxff' ;

Now query the table:

SQL> select * from tmp_tst_tz ;TS                                  TSTZ                                TSLTZ--------------------------- ----------------------------------- -----------------------------------01/29/2006 21:46:29.874272  01/29/2006 21:46:29.874272 -05:00   01/29/2006 21:46:29.8742721 row selected.

So far, so good. Note that the TIMESTAMP WITH LOCAL TIME ZONE data (in column TSLTZ), like the TIMESTAMP data, doesn’t have any time zone information associated with it.

Now, to see the differences between TIMESTAMP WITH TIME ZONE and TIMESTAMP WITH LOCAL TIME ZONE data, change your session time zone (see Sidebar 2 for more information on your session time zone). Go back to Istanbul, which is seven hours ahead of UTC -05:00. Set the time zone to ‘Turkey’ (see Sidebar 3) and select from the table:

SQL> alter session set time_zone='Turkey' ;Session altered.SQL> select * from tmp_tst_tz ;SQL> select * from tmp_nr_tstz;TS                                  TSTZ                                TSLTZ----------------------------------- ----------------------------------- -----------------------------------01/29/2006 21:46:29.874272          01/29/2006 21:46:29.874272 -05:00   01/30/2006 04:46:29.8742721 row selected.

The value Oracle returns in the TSLTZ column has changed! When your session retrieves the TIME STAMP WITH LOCAL TIME ZONE data from the database, Oracle automatically converts it on the fly to the equivalent time in your local time zone. At 21:46 in Toronto, it’s 04:46 in Istanbul; so that’s the time displayed when 21:46 is entered in Toronto time and retrieved in Istanbul time.

Note also that the TIMESTAMP data hasn’t changed. This is an excellent illustration of the importance of using time zones. To return to the call center example from the article introduction, if a call center in Toronto had simply logged a call at 21:46 in a TIMESTAMP column, then an operator in Istanbul following up on that call would think the call had taken place seven hours before it actually did. If the application developer had used TIMESTAMP WITH TIME ZONE, the Istanbul operator could see that the call had taken place at 21:46 EST; if the application developer had used TIMESTAMP WITH LOCAL TIME ZONE, then the operator would see that the call had taken place at 4:46 AM local time.

Of course, application developers can choose TIMESTAMP WITH TIME ZONE and perform their own conversions. The easiest way to do this is to use the “AT TIME ZONE” syntax. This lets you take a TIMESTAMP WITH TIME ZONE and display what it would be in another time zone:

SQL> select tstz at time zone 'Turkey', tsltz from tmp_nr_tstz ;TSTZATTIMEZONE'TURKEY'                                                      TSLTZ--------------------------------------------------------------------------- -----------------------------------01/30/2006 04:46:29.874272 +02:00                                           01/30/2006 04:46:29.8742721 row selected. 

You can display a TIMESTAMP WITH TIME ZONE data type almost as though it were a TIMESTAMP WITH LOCAL TIME ZONE by combining the AT TIME ZONE syntax with the SESSIONTIMEZONE function:

SQL> select tstz at time zone sessiontimezone, tsltz from tmp_nr_tstz; TSTZATTIMEZONESESSIONTIMEZONE                                               TSLTZ--------------------------------------------------------------------------- -----------------------------------01/30/2006 04:46:29.874272 +02:00                                           01/30/2006 04:46:29.8742721 row selected.

The AT TIME ZONE syntax can be applied to any datetime column or expression.

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:001 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 replacefunction convert_time ( datetime in timestamp, tz1 in varchar2, tz2 in varchar2 )return timestamp with time zoneas	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:001 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:001 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:001 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 dualSQL> /TO_TIMESTAMP_TZ('04/02/200601:00:00US/EASTERN','MM/DD/YYYYHH24:MI:SSTZR')+T---------------------------------------------------------------------------04/02/2006 03:13:23.000000000 -04:001 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:001 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”.

Setting Up the Right Time Zone

You easily could run into pitfalls with Daylight Savings Time if your client sessions’ time zones are specified using time-zone displacements instead of time zone region names Unfortunately, most client installations don’t explicitly set the time zone, which causes Oracle to pick up the time zone displacement from the operating system. As a developer, you can get around this by explicitly adding ALTER SESSION SET TIME_ZONE statements to your code. However, if your client sessions are running in different regions, this is impractical. A better solution is to set up client installations so that they are configured with the appropriate time zone name.

For example, a single database that stores call records for call centers in Boston and Istanbul could have two Web servers set up to access it: one in the Boston call center, one in the Istanbul call center. The time zone for the Oracle client installation on the Boston call center’s Web server is ‘US/Eastern’; on the Istanbul center’s Web server, it’s set to ‘Turkey’.

To set up a default time zone on a client installation, use the environment variable ORA_SDTZ. You can set ORA_SDTZ to the default values DB_TZ (to match the database time zone) or OS_TZ (to match the time zone returned by the client’s OS, usually a displacement value), to a displacement value, or to any valid time zone region name:

ORA_SDTZ = 'DB_TZ' | 'OS_TZ' | '[+|-]HH:MI' | 'timezone_name'

In this example, you’d set ORA_SDTZ on the Boston Web server to ‘US/Eastern’, and set ORA_SDTZ on the Istanbul Web server to ‘Turkey’.

For an example of how leaving ORA_SDTZ unset can get you into trouble, see Sidebar 4. Pitfall: DST and the Oracle Scheduler.

Make Your Oracle Code Time Zone Aware

You’ve seen how the TIMESTAMP WITH TIME ZONE and TIMESTAMP WITH LOCAL TIME ZONE data types interact with session and system parameters to provide Oracle’s time zone support, and how Oracle’s daylight savings time support works. Use these data types and parameters to make your Oracle code time zone aware.