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.
