Datatypes - Expert Oracle Database Architecture - page 554

EODA@ORA12CR1> select dbtimezone from dual;

DBTIMEZONE
------------
-07:00

Now, when we dump those values as follows:

EODA@ORA12CR1> select dump(dt), dump(ts1), dump(ts2) from t;

DUMP(DT)
-------------------------------------------------------------------------------
DUMP(TS1)
-------------------------------------------------------------------------------
DUMP(TS2)
-------------------------------------------------------------------------------
Typ=12 Len=7: 120,114,2,27,17,3,33
Typ=181 Len=13: 120,114,2,28,1,3,33,12,162,221,0,137,156
Typ=231 Len=11: 120,114,2,27,18,3,33,12,162,221,0

We can see that, in this case, three totally different date/time representations were stored:

•

DT : This column stored the date/time 27-FEB-2014 16:02:32. The time zone and fractional
seconds are lost because we used the DATE type. No time zone conversions were performed at
all. We stored the exact date/time inserted, but lost the time zone.

•

TS1 : This column preserved the TIME ZONE information and was normalized to be in UTC
with respect to that TIME ZONE . The inserted TIMESTAMP value was in the US/Pacific time zone,
which at the time of this writing was eight hours off UTC. Therefore, the stored date/time was
28-FEB-2014 00:02:32. It advanced our input time by eight hours to make it UTC time, and it
saved the time zone US/Pacific as the last 2 bytes so this data can be properly interpreted later.

TS2 : This column is assumed to be in the database's time zone, which is US/Mountain.
Now, 16:02:32 US/Pacific is 17:02:32 US/Mountain, so that is what was stored in the bytes
...18,3,33... (excess-1 notation; remember to subtract 1).
Since the TS1 column preserved the original time zone in the last 2 bytes, we'll see the following upon retrieval:

•

EODA@ORA12CR1> select ts1, ts2 from t;

TS1
---------------------------------------------------------------------------
TS2
---------------------------------------------------------------------------
27-FEB-14 04.02.32.212000 PM US/PACIFIC
27-FEB-14 05.02.32.212000 PM

The database would be able to show that information, but the TS2 column with the LOCAL TIME ZONE (the time
zone of the database) shows the time in database's time zone, which is the assumed time zone for that column (and
in fact all columns in this database with the LOCAL TIME ZONE ). My database was in the US/Mountain time zone, so
16:02:32 US/Pacific on the way in is now displayed as 5:00 p.m. Mountain time on the way out.