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.
Show valid time zone names for MST
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.
Check the database time zone
Changing the database time zone
Validate the change in DBTIMEZONE
Switch back DBTIMEZONE to hh:mm format
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.
Set time zone at the session level
Check session level time zone
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.
Get current system timestamp with time zone information
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.
Local timestamp for session
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.
Create table with different TIMESTAMP columns
Inserting SYSTIMESTAMP into DATE_TABLE
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.