Oracle Time Zone

Just when you thought you knew where you were, Oracle has implemented time zones to totally zone us out. Come along and let’s figure where we are on this great planet.

A few articles back I wrote on a comparison between the DATE datatype and the TIMESTAMP datatype. This article goes one step further and explores Oracle’s TIME ZONE feature of the TIMESTAMP datatype. In particular, Oracle has added the “WITH TIME ZONE” and the “WITH LOCAL TIME ZONE” to the TIMESTAMP datatype.

Valid Time Zones

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

TIMESTAMP datatype with TIME ZONE Information

At the heart of storing time zone information within a table, Oracle has defined the TIMESTAMP data type. This is Oracle’s great new hope for giving the old DATE datatype a more granular mechanism of holding not only date and time information but also fractional seconds. Look at A Comparison of Oracle’s DATE and TIMESTAMP Datatypes for a discussion of this databtype. For the remainder of this article and for the purposes of exploiting the time zone options for the TIMESTAMP datatype, I have created a table as defined in Table 1. In Listing 10, I have created a quick example of how the TIMESTAMP WITH TIME ZONE and TIMESTAMP WITH LOCAL TIME ZONE datatypes will behave when used to store information. To begin with I have set the DBTIMEZONE and SESSION TIMEZONE to be the same value ‘-07:00’. I then insert into the DATE_TABLE the value of a system time stamp. I then change the time zone for the current session only to be ‘-03:00’. This will set the current session four hours ahead of the database time zone. I then insert into the DATE_TABLE a row that will hold the current session timestamp. The main idea to grasp here is that the column with datatype TIMESTAMP WITH TIME ZONE stores and displays the explicit time supplied from the INSERT statement. The column with datatype TIMESTAMP WITH LOCAL TIME ZONE stores the explicit time supplied but will display a value that is relative to the current session time zone. This means that if you want hard and fast TIMESTAMPS with time zone information stored, you should use the TIMESTAMP WITH TIME ZONE datatype. If you are concerned more with giving date and time information to customers in different time zones and represented in their local time, you should use the TIMESTAMP WITH LOCAL TIME ZONE datatype.

Table 1:

Create table with different TIMESTAMP columns

create table date_table (
       time_stamp_tz   TIMESTAMP WITH TIME ZONE,
       time_stamp_ltz  TIMESTAMP WITH LOCAL TIME ZONE);

LISTING 10
Inserting SYSTIMESTAMP into DATE_TABLE

SQL> select dbtimezone,sessiontimezone from dual;
DBTIMEZONE SESSIONTIMEZONE
---------- ----------------
-07:00     -07:00

SQL> insert into date_table values (SYSTIMESTAMP,SYSTIMESTAMP);
1 row created.

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

SQL> select dbtimezone,sessiontimezone from dual;
DBTIMEZONE SESSIONTIMEZONE
---------- ----------------
-07:00     -03:00


SQL> insert into date_table values (LOCALTIMESTAMP,LOCALTIMESTAMP );
1 row created.

SQL> select time_stamp_tz, time_stamp_ltz from date_table;
TIME_STAMP_TZ                         TIME_STAMP_LTZ
------------------------------------- ------------------------------
01-SEP-03 10.57.36.642000 AM -07:00   01-SEP-03 02.57.36.642000 PM
01-SEP-03 02.57.48.549000 PM -03:00   01-SEP-03 02.57.48.549000 PM

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


SQL> select time_stamp_tz, time_stamp_ltz from date_table;
TIME_STAMP_TZ                         TIME_STAMP_LTZ
------------------------------------- ------------------------------
01-SEP-03 10.57.36.642000 AM -07:00   01-SEP-03
10.57.36.642000 AM
01-SEP-03 02.57.48.549000 PM -03:00   01-SEP-03
10.57.48.549000 AM

We have all grown up and accepted time zones. It has always been easy to do the hour difference in our heads when calling someone or traveling to a different time zone. For some reason the implementation of time zones through offsets of the GMT/UTC made it difficult for me to conceptualize and figure out where and what time it truly is. I would suggest that you play around with the example that I have given you so that you may become acquainted with this feature and where on earth you are.

» See All Articles by Columnist James Koopmann

James Koopmann
James Koopmann
James Koopmann has fourteen years of database design, development and performance tuning experience. In addition, he has extensive database administration experience in Oracle and other relational databases in production environments, specializing in performance tuning of database engines and SQL based applications. Koopmann is an accomplished author with several technical papers in various Oracle related publications such as Oracle Magazine, Oracle Professional and SQL>UPDATE_RMOUG. He is a featured author and database expert for DatabaseJournal, a member of the editorial review committee for Select Journal (The Magazine for the International Oracle Users Group), an Oracle Certified Professional DBA and noted speaker at local Oracle User Groups around the country.

Get the Free Newsletter!

Subscribe to Cloud Insider for top news, trends & analysis

Latest Articles