Saturday, 14 February 2009

Time Zones in Oracle

The TIMESTAMP datatype was introduced in Oracle 9i. TIMESTAMPs differ from DATEs in that they allow fractions of a second to 9 decimal places and can be time zone aware.

Let's see how TIMESTAMPs and DATEs behave when they are simultaneously accessed by a user in the United States and a user in the United Kingdom. We can simulate this situation in a couple of sessions by setting the time zone of one session to US/Pacific and the other to GB. It is 8am in the US and 4pm in the UK.

Set Up

First we create this table in a database which is located in the UK.
CREATE TABLE t1 (
d DATE,
t TIMESTAMP,
tz TIMESTAMP WITH TIME ZONE,
ltz TIMESTAMP WITH LOCAL TIME ZONE
);
As you can see there are four datetime datatypes; DATE, TIMESTAMP, TIMESTAMP WITH TIME ZONE and TIMESTAMP WITH LOCAL TIME ZONE.

Next we set up a couple of sessions, one with a US/Pacific time zone and the other with a GB time zone. We also set the default date and time formats for the session.

US Session
ALTER SESSION SET time_zone = 'US/Pacific';

ALTER SESSION SET nls_date_format='DD-Mon-RR hh24:MI';
ALTER SESSION SET nls_timestamp_format='DD-Mon-RR HH24:MI';
ALTER SESSION SET nls_timestamp_tz_format='DD-Mon-RR HH24:MI TZR';
UK Session
ALTER SESSION SET time_zone = 'GB';

ALTER SESSION SET nls_date_format='DD-Mon-RR hh24:MI';
ALTER SESSION SET nls_timestamp_format='DD-Mon-RR HH24:MI';
ALTER SESSION SET nls_timestamp_tz_format='DD-Mon-RR HH24:MI TZR';

Database and Session Time Zones

There is a time zone associated with the database and a time zone associated with a session. We can display these values using the DBTIMEZONE and SESSIONTIMEZONE functions.

US session
SELECT dbtimezone, sessiontimezone FROM dual;

DBTIMEZONE SESSIONTIMEZONE
---------- -----------------
+00:00 US/Pacific

1 rows selected
UK session
SELECT dbtimezone, sessiontimezone FROM dual;

DBTIMEZONE SESSIONTIMEZONE
---------- -----------------
+00:00 GB

1 rows selected

Current Date and Time

We can also retrieve the current date or timestamp for the database and the session. SYSDATE and SYSTIMESTAMP return the database date and time and CURRENT_DATE, CURRENT_TIMESTAMP and LOCALTIMESTAMP return the session time. The difference between LOCALTIMESTAMP and CURRENT_TIMESTAMP is that LOCALTIMESTAMP returns a TIMESTAMP value while CURRENT_TIMESTAMP returns a TIMESTAMP WITH TIME ZONE value.

US session
SELECT sysdate, systimestamp FROM dual;

SYSDATE SYSTIMESTAMP
---------------------- --------------------------
05-Nov-2008 16:02 05-Nov-2008 16:02 +00:00

1 rows selected


SELECT current_date, current_timestamp, localtimestamp FROM dual;

CURRENT_DATE CURRENT_TIMESTAMP LOCALTIMESTAMP
--------------------- ------------------------------- ------------------
05-Nov-2008 08:02 05-Nov-2008 08:02 US/PACIFIC 05-Nov-2008 08:02

1 rows selected
UK session
SELECT sysdate, systimestamp FROM dual;

SYSDATE SYSTIMESTAMP
---------------------- --------------------------
05-Nov-2008 16:02 05-Nov-2008 16:02 +00:00

1 rows selected


SELECT current_date, current_timestamp, localtimestamp FROM dual;

CURRENT_DATE CURRENT_TIMESTAMP LOCALTIMESTAMP
--------------------- ----------------------- -------------------
05-Nov-2008 16:02 05-Nov-2008 16:02 GB 05-Nov-2008 16:02

1 rows selected
The SYSDATE and SYSTIMESTAMP values are the same for both sessions as you would expect but the CURRENT_DATE, CURRENT_TIMESTAMP and LOCALTIMESTAMP are different in the US and the UK because they reflect the time zone of the session.

Date and Timestamp

Now, let's insert some data into our table from the US session.

US session
INSERT INTO t1 (d, t, tz, ltz) VALUES (
TIMESTAMP '2008-11-05 11:22:00',
TIMESTAMP '2008-11-05 11:22:00',
TIMESTAMP '2008-11-05 11:22:00',
TIMESTAMP '2008-11-05 11:22:00'
);

COMMIT;


SELECT d, t FROM t1;

D T
----------------- -----------------
05-Nov-08 11:22 05-Nov-08 11:22

1 rows selected
The DATE and TIMESTAMP datatypes have no concept of time zones. They return the exact time that was entered, regardless of the time zone of the client. Therefore, the values will be the same for the US session and the UK session.

But what about timestamps with time zones?

US session
SELECT tz, ltz FROM t1;

TZ LTZ
---------------------------- -----------------
05-Nov-08 11:22 US/PACIFIC 05-Nov-08 11:22

1 rows selected
UK session
SELECT tz, ltz FROM t1;

TZ LTZ
---------------------------- -----------------
05-Nov-08 11:22 US/PACIFIC 05-Nov-08 19:22

1 rows selected

The TIMESTAMP WITH TIME ZONE datatype stores the time zone offset in its value, so the output of TZ is the same in both sessions. The time zone offset is the difference (in hours and minutes) between local time and UTC. Whenever the timestamp value is retrieved, the time zone is retrieved with it.

The LTZ column, on the other hand, displays a different value in each session. When a timestamp is stored in a TIMESTAMP WITH LOCAL TIME ZONE datatype, it is normalized to the database time zone. The time zone offset is not stored as part of the column data. When the user retrieves the data, Oracle automatically converts the value to the local session time zone.

A TIMESTAMP WITH LOCAL TIME ZONE value is the only timestamp datatype that will adjust the display value for a session time zone.

Explicit Conversion

So how do we convert a timestamp to a specific time zone in SQL? The answer is the AT TIME ZONE clause.

Suppose we wish to find out the current time in the US and the current time in Japan.

US session
SELECT current_timestamp us, 
current_timestamp AT TIME ZONE 'Japan' japan
FROM dual;

US JAPAN
---------------------------- ------------------------
05-Nov-08 03:15 US/PACIFIC 05-Nov-08 19:15 JAPAN

1 rows selected

Timestamp Comparisons

Two TIMESTAMP WITH TIME ZONE values are considered identical if they represent the same instant in UTC, regardless of the time zone offset stored in the data.

For example, using the data we inserted earlier.

US session
SELECT tz FROM t1 WHERE tz = TIMESTAMP '2008-11-05 11:22:00';

TZ
----------------------------
05-Nov-08 11:22 US/PACIFIC

1 rows selected
The date in the TZ column and the timestamp literal both correspond to a UTC time of 05-Nov-08 19.22

UK session
SELECT tz FROM t1 WHERE tz = TIMESTAMP '2008-11-05 11:22:00';

TZ
-------------

0 rows selected


SELECT tz FROM t1 WHERE tz = TIMESTAMP '2008-11-05 19:22:00';

TZ
----------------------------
05-Nov-08 11:22 US/PACIFIC

1 rows selected


SELECT tz FROM t1 WHERE tz = TIMESTAMP '2008-11-05 11:22:00 US/Pacific';

TZ
----------------------------
05-Nov-08 11:22 US/PACIFIC

1 rows selected


SELECT tz FROM t1
WHERE tz = TIMESTAMP '2008-11-05 11:22:00' + INTERVAL '8' HOUR;

TZ
----------------------------
05-Nov-08 11:22 US/PACIFIC

1 rows selected
In the first statement, the TZ column has a UTC time of 05-Nov-08 19.22 but the timestamp literal has a UTC time of 05-Nov-08 11.22. The subsequent SQL statements adjust the timestamp literal to match the value in the table.

A Word About Timestamp Literals

A timestamp literal takes the following format.
TIMESTAMP '2008-11-05 11:22:00'
or
TIMESTAMP '2008-11-05 11:22:00 GB'
or
TIMESTAMP '2008-11-05 11:22:00 -08:00'

We could just use a character string.
SELECT tz FROM t1 WHERE tz = '5-Nov-08 11:22 US/Pacific'; 

TZ
----------------------------
05-Nov-08 11:22 US/PACIFIC

1 rows selected
However, I would recommend against this approach as we are no longer using a timestamp literal. Instead, we are using a character literal which is implicitly converted to a timestamp when it is compared with a date.

The problem with this technique is that the query can return different results depending on the value of the default timestamp format model for the session.
ALTER SESSION SET nls_timestamp_tz_format='DD-Mon-RR HH24:MI TZR';

SELECT tz FROM t1 WHERE tz = '5-Nov-08 11:22 US/Pacific';

TZ
----------------------------
05-Nov-08 11:22 US/PACIFIC

1 rows selected
Now we run the same SQL but with a different timestamp format.
ALTER SESSION SET nls_timestamp_tz_format='DD-Mon-YYYY HH24:MI TZR';

SELECT tz FROM t1 WHERE tz = '5-Nov-08 11:22 US/Pacific';

TZ
-------------

0 rows selected
For this reason, be sure to use a date or timestamp literal or an explicit conversion using the TO_DATE, TO_TIMESTAMP or TO_TIMESTAMP_TZ functions.
SELECT tz FROM t1 WHERE tz = 
TO_TIMESTAMP_TZ('5-Nov-08 11:22 US/Pacific', 'DD-Mon-YY HH24:MI TZR');

TZ
----------------------------
05-Nov-08 11:22 US/PACIFIC

1 rows selected

2 comments:

Anonymous said...

Great Post. Thanks a bunch.

Shashi said...

SYSTIMESTAMP: returns the server time with data+time+zone.

CURRENTTIMESTAMP: returns session time with date+time+zone.

More..