6.1 Time Zones

In the global economy, business is carried out across geographical boundaries and time zones. It is common for a customer in Los Angeles to order an item through a supplier's web site, and the supplier's database could be located in New York. A manufacturer in China could update the status of an order of one of its U.S. customers. Conducting business across the globe is a requirement in today's global economy, and the evolution of the Internet and related technologies have made it simple. Databases can't be far behind, can they?

Oracle facilitates global business through its support for time zones. With Oracle 9i Database and higher, a database and a session can be associated with time zones. Having database and session time zones enables users in geographically distant regions to exchange temporal data with the database without having to bother about the time differences between the location of their clients and the location of the database server.

The list of valid time zone region names is provided in the data dictionary view V$TIMEZONE_NAMES.

6.1.1 Database Time Zone

The time zone of a database is usually set at the time of creation of the database. Alternatively, a database administrator can change the time zone using the ALTER DATABASE command, after a database is created. Both CREATE DATABASE and ALTER DATABASE commands take an optional SET TIME_ZONE clause:

SET TIME_ZONE = '+ | - HH:MI' | 'time_zone_region'

You can specify a time zone in one of two ways:

By specifying a displacement from Coordinated Universal Time (UTC) in hours and minutes. For example, United States Eastern Standard Time is UTC -05:00.
By specifying a time zone name or time zone abbreviation (columns TZNAME and TZABBREV in V$TIMEZONE_NAMES, respectively). Every time zone is given a name and abbreviation. For example, "U.S./Eastern" is the time zone name, and EST is the time zone abbreviation for Eastern Standard Time. You can use either the time zone name or the abbreviation to set the time zone of a database.

The following examples use the SET TIME_ZONE clause to set the time zone of a database:

CREATE DATABASE  . . .  SET TIME_ZONE = '-05:00';



ALTER DATABASE  . . .  SET TIME_ZONE = 'EST';

Both of these examples set the time zone to Eastern Standard Time. The first example uses a displacement (-05:00) from UTC. The second example uses the time zone abbreviation (EST). EST is 5 hours behind UTC, and is therefore equivalent to "-5:00".

If you do not explicitly set the database time zone, Oracle defaults to the operating system time zone. If the operating system time zone is not a valid Oracle time zone, UTC is used as the default time zone.

6.1.2 Session Time Zone

Each session can have a time zone as well. You can set the time zone of a session by using the ALTER SESSION SET TIME_ZONE statement. The syntax for the SET TIME_ZONE clause in the ALTER SESSION statement is the same as that in the CREATE DATABASE and ALTER DATABASE statements.

The following example shows two ways to set the time zone of a session to Pacific Standard Time:

ALTER SESSION SET TIME_ZONE = '-08:00';



ALTER SESSION SET TIME_ZONE = 'PST';

To set the session time zone to the local operating system time zone (e.g., the time zone of a PC initiating a remote user session), you can use the LOCAL keyword in the SET TIME_ZONE clause, as in the following example:

ALTER SESSION SET TIME_ZONE = LOCAL;

To set the session time zone to the database time zone, use the DBTIMEZONE keyword in the SET TIME_ZONE clause, as in the following example:

ALTER SESSION SET TIME_ZONE = DBTIMEZONE;

If you do not explicitly set the session time zone, Oracle defaults to the local operating system time zone. If the local operating system time zone is not a valid Oracle time zone, UTC is used as the default session time zone.