Understanding Time Zones

The Oracle9i database introduced the ability to use different time zones. A time zone is an offset from the time in Greenwich, England. The time in Greenwich was once known as Greenwich Mean Time (GMT), but is now known as Coordinated Universal Time (UTC, which comes from the French initials for the words). You specify a time zone using either an offset from UTC or the name of the region. When you specify an offset, you use HH:MI prefixed with a plus or minus sign:

 +-HH:MI 

where

• + or “ indicates an increase or decrease for the offset from UTC

• HH:MI indicates the time zone hour and minute for the offset

  Note  

  The time zone hour and minute use the format parameters TZH and TZR shown earlier in Table 4-2.

The following examples show offsets of “7 hours behind UTC and +2 hours 15 minutes ahead of UTC:

 -07:00 +02:15 

You may also specify a time zone using the name of a region. For example, PST indicates Pacific Standard Time, which is seven hours behind UTC. EST indicates Eastern Standard Time, which is four hours behind UTC.

Note  

The time zone region uses the format parameter TZR shown earlier in Table 4-2.

Time Zone “ Related Functions

There are a number of functions that are related to time zones; these functions are shown in Table 4-5.

Table 4-5: Time Zone “Related Functions

Function	Description
CURRENT_DATE()	Returns the current date in the local time zone set for the database session.
DBTIMEZONE()	Returns the time zone for the database.
NEW_TIME( x , time_zone1, time_zone2 )	Converts x from time_zone1 to time_zone2 and returns the new datetime.
SESSIONTIMEZONE()	Returns the time zone for the database session.
TZ_OFFSET( time_zone )	Returns the offset for time_zone in hours and minutes.

You ll learn more about the functions shown in Table 4-5 in the following sections.

The Database Time Zone and Session Time Zone

If you re working for a large worldwide organization, the database you access may be located in a different time zone than your local time zone. The time zone for the database is known as the database time zone , and the time zone set for your database session is known as the session time zone . You ll learn about the database and session time zones in the following sections.

The Database Time Zone

The database time zone is controlled using the TIME_ZONE database parameter. Your DBA can change the setting of the TIME_ZONE parameter in the database s init.ora or spfile.ora file. You can get the database time zone using the DBTIMEZONE() function:

  SELECT DBTIMEZONE   FROM dual;  DBTIME ------ -05:00 

The Session Time Zone

By default, your session time zone is the same as the database time zone. You can change your session time zone using the ALTER SESSION statement to set the TIME_ZONE parameter. For example, the following ALTER SESSION statement sets the local time zone to Pacific Standard Time (PST):

 ALTER SESSION SET TIME_ZONE = 'PST'; 

Note  

Setting the session time zone doesn t change the database time zone.

You can get your session time zone using the SESSIONTIMEZONE() function:

  SELECT SESSIONTIMEZONE   FROM dual;  SESSIONTIMEZONE --------------- PST 

Getting the Current Date in the Session Time Zone

Earlier, you saw how the SYSDATE() function is used to get the date set for the operating system where the database resides. This gives you the date in the database time zone. You can get the date in your session time zone using the CURRENT_DATE() function. For example:

  SELECT CURRENT_DATE   FROM dual;  CURRENT_D --------- 06-OCT-03 

Obtaining Time Zone Offsets

You can get the time zone offset hours using the TZ_OFFSET() function, passing the time zone region name to TZ_OFFSET(). For example, the following query uses TZ_OFFSET() to get the time zone offset hours for PST, which is “7 hours:

  SELECT TZ_OFFSET('PST')   FROM dual;  TZ_OFFS ------- -07:00 

Obtaining Time Zone Names

You can obtain all the time zone names by selecting all the rows from v$timezone_names. To query v$timezone_names you should first connect to the database as the system user . The following query shows some of the rows from v$timezone_names:

  SELECT *   FROM v$timezone_names;  TZNAME              TZABBREV ------------------- -------- Africa/Cairo             LMT Africa/Cairo             EET Africa/Cairo            EEST Africa/Tripoli           LMT Africa/Tripoli           CET Africa/Tripoli          CEST Africa/Tripoli           EET America/Adak             LMT 

You may use any of the TZABBREV column values as your time zone setting.

Converting a Datetime from One Time Zone to Another

You use the NEW_TIME() function to convert a datetime from one time zone to another. For example, the following query uses NEW_TIME() to convert 7:45 P.M. on May 13, 2006, from PST to EST:

  SELECT TO_CHAR(NEW_TIME(TO_DATE('25-MAY-2006 19:45',   'DD-MON-YYYY HH24:MI'), 'PST', 'EST'), 'DD-MON-YYYY HH24:MI')   FROM dual;  TO_CHAR(NEW_TIME( ----------------- 25-MAY-2006 22:45 

EST is three hours ahead of PST, and therefore three hours are added to 7:45 P.M. to give 10:45 P.M. ”or 22:45 in 24-hour format.