Database Reference
In-Depth Information
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
DATE
s from one time zone to
another. Now it's the job of the database, and it can store data in multiple time zones:
EODA@ORA12CR1> create table t
2 ( ts1 timestamp with time zone,
3 ts2 timestamp with time zone
4 )
5 /
Table created.
EODA@ORA12CR1> insert into t (ts1, ts2)
2 values ( timestamp'2014-02-27 16:02:32.212 US/Eastern',
3 timestamp'2014-02-27 16:02:32.212 US/Pacific' );
1 row created.
And perform correct
TIMESTAMP
arithmetic on them:
EODA@ORA12CR1> select ts1-ts2 from t;
TS1-TS2
---------------------------------------------------------------------------
-000000000 03:00:00.000000
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
TIMESTAMPS WITH TIME ZONE
types, Oracle automatically converts both types to UTC time first and then performs the
operation.
TIMESTAMP WITH LOCAL TIME ZONE Type
This type works much like the
TIMESTAMP
column. It is a 7- or 11-byte field (depending on the precision of the
TIMESTAMP
), but it is normalized to be stored with the local database's time zone. To see this, we'll use the
DUMP
command once again. First, we create a table with three columns—a
DATE
, a
TIMESTAMP WITH TIME ZONE
, and a
TIMESTAMP WITH LOCAL TIME ZONE
—and then we insert the same value into all three columns:
EODA@ORA12CR1> create table t
2 ( dt date,
3 ts1 timestamp with time zone,
4 ts2 timestamp with local time zone
5 )
6 /
Table created.
EODA@ORA12CR1> insert into t (dt, ts1, ts2)
2 values ( timestamp'2014-02-27 16:02:32.212 US/Pacific',
3 timestamp'2014-02-27 16:02:32.212 US/Pacific',
4 timestamp'2014-02-27 16:02:32.212 US/Pacific' );
1 row created.
