TimeZones in Oracle

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

Let's see how TIMESTAMP s and DATE s 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