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
» 
Comment and Contribute
