You have two options when setting which time zone the
database belongs to. You can either qualify it as a displacement from GMT/UTC
in the format of 'hh:mm' or you can specify it as a name that has an entry in
the V$TIMEZONE table. Listing 1 shows the SQL I
used to display the valid time zone name in the Mountain Standard Time Zone.

Listing 1

Show valid time zone names for MST

SQL> select tzname,tzabbrev 

       from V$TIMEZONE_NAMES 

      where tzabbrev = 'MST'





TZNAME                    TZABBREV

------------------------- ----------

America/Denver            MST

America/Edmonton          MST

America/Ensenada          MST

America/Mazatlan          MST

America/Mexico_City       MST

America/Phoenix           MST

America/Regina            MST

America/Shiprock          MST

America/Tijuana           MST

Canada/East-Saskatchewan  MST

Canada/Mountain           MST

Canada/Saskatchewan       MST

Europe/Moscow             MST

Mexico/BajaNorte          MST

Mexico/BajaSur            MST

Mexico/General            MST

MST                       MST

MST7MDT                   MST

Navajo                    MST

US/Arizona                MST

US/Mountain               MST

W-SU                      MST

DBTIMEZONE

At the core of time zones is the database time zone. You can
look at the time zone that was selected at database creation by issuing the SQL
in Listing 2. If you want to change the DBTIMEZONE
you will need to issue an ALTER DATBASE command such as in Listing 3. In order for the new DBTIMEZONE to take
effect, you must bounce the database. In addition, if you have any data type
columns that are of the TIMESTAMP with TIME ZONE defined, you will not be able
to reset the DBTIMEZONE. After bouncing the database, you can issue the SQL in Listing 4 to validate that the change has taken place.
If you wanted to switch back to the "hh:mm" format for DBTIMEZONE,
just issue the SQL in Listing 5.

Listing 2

Check the database time zone

SQL> select DBTIMEZONE from dual;

DBTIME

------

-07:00

Listing 3

Changing the database time zone

SQL> ALTER database SET TIME_ZONE = 'America/Denver';

Database altered.

Listing 4

Validate the change in DBTIMEZONE

SQL> select DBTIMEZONE from dual;

DBTIMEZONE

---------------

America/Denver

Listing 5

Switch back DBTIMEZONE to hh:mm format

SQL> ALTER database SET TIME_ZONE = '-07:00';

SESSIONTIMEZONE

If you do not want to use the database time zone, Oracle
gives you the option to set the time zone at the session level. Issue the SQL
in Listing 6 if you want a different time zone for
the current session to which you are connected. You can check the session time
zone by issuing the SQL in Listing 7.

Listing 6

Set time zone at the session level

SQL> alter session set TIME_ZONE='-03:00';

Session altered.

Listing 7

Check session level time zone

SQL> select SESSIONTIMEZONE from dual;

SESSIONTIMEZONE

-------------------------------------------------

-03:00

SYSTIMESTAMP

Just as there is a call to SYSDATE to get the current system
date and time, there is a call to get the current system date, time and time
zone. The data type returned is of TIMESTAMP WITH TIME ZONE. Issue the SQL in Listing 8 to get the current system time information.

Listing 8

Get current system timestamp with time zone information

SQL> select SYSTIMESTAMP from dual;

SYSTIMESTAMP

-----------------------------------------------



01-SEP-03 10.53.13.574000 AM -07:00

CURRENT_TIMESTAMP

The counter part of the SYSTIMESTAMP function for session
current date and time is the CURRENT_TIMESTAMP function call. This will return
what the current time stamp of the session is in relation to the session time
zone (SESSIONTIMEZONE). Therefore, if we have the session time zone as defined
in Listing 6, we can check the timestamp of the
session by issuing the SQL in Listing 9.

Listing 9

Local timestamp for session

SQL> select CURRENT_TIMESTAMP from dual;

CURRENT_TIMESTAMP

-------------------------------------------------



01-SEP-03 02.53.33.753000 PM –03:00