TIMESTAMP WITH TIME ZONE Type-Datatypes
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 2 bytes being used to preserve the time zone information. It differs from a TIMESTAMP structurally only by the addition of these 2 bytes:
$ sqlplus eoda/foo@PDB1
SQL> create table t( ts timestamp,ts_tz timestamp with time zone ); Table created.
SQL> insert into t ( ts, ts_tz ) values ( systimestamp, systimestamp ); 1 row created.
SQL> select * from t;TS TS_TZ
SQL> select dump(ts) dump, dump(ts_tz) dump from t;UMP DUMP
Typ=180 Len=11: 120,121,7,18,1,6,16,48,196,170,56
Typ=181 Len=13: 120,121,7,18,1,6,16,48,196,170,56,20,60
Upon retrieval, the default TIMESTAMP WITH TIME ZONE format included the time zone information (I was on US Mountain Daylight Time when this was executed).
TIMESTAMP WITH TIME ZONEs 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 stores two more bytes than the TIMESTAMP column. 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 book; 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.
Before time zone support was built into a datatype, it would have been an application function to store the DATE and in another column the time zone information, and then supply functions to convert DATEs from one time zone to another. Now it’s the job of the database, and it can store data in multiple time zones:
$ sqlplus eoda/foo@PDB1
SQL> create table t( ts1 timestamp with time zone,ts2 timestamp with time zone);Table created.
SQL> insert into t (ts1, ts2)values ( timestamp’2014-02-27 16:02:32.212 US/Eastern’, timestamp’2014-02-27 16:02:32.212 US/Pacific’ );1 row created.
And perform correct TIMESTAMP arithmetic on them:
SQL> select ts1-ts2 from t;TS1-TS2
Since there is a three-hour time difference between those two time zones, even though they show the same time of 16:02:32.212, the interval reported is a three-hour difference. When performing TIMESTAMP arithmetic on TIMESTAMP WITH TIME ZONE types, Oracle automatically converts both types to UTC time first and then performs the operation.