7-12. Associating a Time Zone with a Date and Time
Problem
You want to associate a time zone with a given date and time in order to be more precise.
Solution
Create a code block that declares a field as type TIMESTAMP WITH TIME ZONE. Assign a TIMESTAMP to the newly declared field within the body of the code block. After doing so, the field that you declared will contain the date and time of the TIMESTAMP that you assigned along with the associated time zone. The following example demonstrates a code block that performs this technique using the SYSTIMESTAMP:
DECLARE
time TIMESTAMP WITH TIME ZONE;
BEGIN
time := SYSTIMESTAMP;
DBMS_OUTPUT.PUT_LINE(time);
END;
The results that will be displayed via the call to DBMS_OUTPUT should resemble something similar to the following:
29-AUG-10 10.27.58.639000 AM -05:00
PL/SQL procedure successfully completed.
How It Works
Prior to the TIMESTAMP datatype being introduced in Oracle 9i, the DATE type was the only way to work with dates. There were limited capabilities provided, and later the TIMESTAMP was created to fill those gaps. For those needing to make use of time zones, Oracle created the TIMESTAMP WITH TIME ZONE and TIMESTAMP WITH LOCAL TIME ZONE datatypes. Both of these datatypes provide a time zone to be associated with a given date, but they work a bit differently. When you specify the WITH TIME ZONE option, the time zone information is stored within the database along with the hours, minutes, and so on. However, if you specify the WITH LOCAL TIME ZONE option, the time zone information is not stored within the database, but rather it is calculated each time against a baseline time zone, which determines the time zone of your current session.
In the solution to this recipe, the time zone information is stored within the database along with the rest of the date and time associated with the TIMESTAMP.