Datatypes - Expert Oracle Database Architecture - page 552

TIMESTAMP WITH TIME ZONE Type
The TIMESTAMP WITH TIME ZONE type inherits all of the qualities of the TIMESTAMP type and adds time zone support.
The TIMESTAMP WITH TIME ZONE type consumes 13 bytes of storage, with the extra two bytes being used to
preserve the time zone information. It differs from a TIMESTAMP structurally only by the addition of these 2 bytes:
EODA@ORA12CR1> create table t
2 (
3 ts timestamp,
4 ts_tz timestamp with time zone
5 )
6 /
Table created.
EODA@ORA12CR1> insert into t ( ts, ts_tz )
2 values ( systimestamp, systimestamp );
1 row created.
EODA@ORA12CR1> select * from t;
TS TS_TZ
----------------------------------- ----------------------------------------
02-JAN-14 03.02.51.890565 PM 02-JAN-14 03.02.51.890565 PM -07:00
EODA@ORA12CR1> select dump(ts) dump, dump(ts_tz) dump from t;
DUMP
------------------------------------------------------------
DUMP
------------------------------------------------------------
Typ=180 Len=11: 120,114,1,2,16,3,52,53,20,241,136
Typ=181 Len=13: 120,114,1,2,23,3,52,53,20,241,136,13,60
Upon retrieval, the default TIMESTAMP WITH TIME ZONE format included the time zone information
(I was on U.S. Mountain Standard Time when this was executed).
TIMESTAMP WITH TIME ZONE s store the data in whatever time zone was specified when the data was stored.
The time zone becomes part of the data itself. Note how the TIMESTAMP WITH TIME ZONE field stored ...23,3,52...
for the hour, minutes, and seconds (in excess-1 notation, so that is 22:02:51), whereas the TIMESTAMP field stored
simply ...16,3,52... , which is 15:02:51 —the exact time in the string we inserted. The TIMESTAMP WITH TIME ZONE
had seven hours added to it, in order to store in GMT (also known as UTC) time. The trailing 2 bytes are used upon
retrieval to properly adjust the TIMESTAMP value.
It is not my intention to cover all of the nuances of time zones here in this topic; that is a topic well covered
elsewhere. To that end, I'll just point out that there is support for time zones in this datatype. This support is more
relevant in applications today than ever before. In the distant past, applications were not nearly as global as they are
now. In the days before widespread Internet use, applications were many times distributed and decentralized, and the
time zone was implicitly based on where the server was located. Today, with large centralized systems being used by
people worldwide, the need to track and use time zones is very relevant.