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.