yes, it is time zone aware. Here is an excerpt from the 2nd edition of Expert Oracle Database Architecture, it shows this at work:
<quote>
<b>
TIMESTAMP WITH TIME ZONE Type</b>
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:
ops$tkyte@ORA11GR2> create table t
2 (
3 ts timestamp,
4 ts_tz timestamp with time zone
5 )
6 /
Table created.
ops$tkyte@ORA11GR2> insert into t ( ts, ts_tz )
2 values ( systimestamp, systimestamp );
1 row created.
ops$tkyte%ORA11GR2> select * from t;
TS TS_TZ
---------------------------- -----------------------------------
12-APR-10 07.09.52.502950 PM 12-APR-10 07.09.52.502950 PM -04:00
ops$tkyte%ORA11GR2> select dump(ts) dump, dump(ts_tz) dump from t;
DUMP DUMP
----------------------------------- -----------------------------------
Typ=180 Len=11: 120,110,4,12,20,10, Typ=181 Len=13: 120,110,4,12,24,10,
53,29,250,104,112 53,29,250,104,112,16,60
So, as you can see, upon retrieval the default TIMESTAMP WITH TIME ZONE format included the time zone information (I was on East Coast U.S. time during daylight savings 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 field stored …24,10,53… for the hour, minutes, and seconds (in excess-1 notation, so that is 23:09:52), whereas the TIMESTAMP field stored simply …20,10,53…, which is 19:09:52 —the exact time in the string we inserted. The TIMESTAMP WITH TIME ZONE had four 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 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. A decade or so ago, 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 that is the job of the database, and it is able to store data in multiple time zones:
ops$tkyte%ORA11GR2> create table t
2 ( ts1 timestamp with time zone,
3 ts2 timestamp with time zone
4 )
5 /
Table created.
ops$tkyte%ORA11GR2> insert into t (ts1, ts2)
2 values ( timestamp'2010-02-27 16:02:32.212 US/Eastern',
3 timestamp'2010-02-27 16:02:32.212 US/Pacific' );
1 row created.
and perform correct TIMESTAMP arithmetic on them:
ops$tkyte%ORA11GR2> 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.
<b>TIMESTAMP WITH LOCAL TIME ZONE Type</b>
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 in 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:
ops$tkyte@ORA11GR2> create table t
2 ( dt date,
3 ts1 timestamp with time zone,
4 ts2 timestamp with local time zone
5 )
6 /
Table created.
ops$tkyte%ORA11GR2> insert into t (dt, ts1, ts2)
2 values ( timestamp'2010-02-27 16:02:32.212 US/Pacific',
3 timestamp'2010-02-27 16:02:32.212 US/Pacific',
4 timestamp'2010-02-27 16:02:32.212 US/Pacific' );
1 row created.
ops$tkyte%ORA11GR2> select dbtimezone from dual;
DBTIMEZONE
----------
US/Eastern
Now, when we dump those values as follows:
ops$tkyte%ORA11GR2> select dump(dt), dump(ts1), dump(ts2) from t;
DUMP(DT)
-------------------------------------------------------------------------------
DUMP(TS1)
-------------------------------------------------------------------------------
DUMP(TS2)
-------------------------------------------------------------------------------
Typ=12 Len=7: 120,110,2,27,17,3,33
Typ=181 Len=13: 120,110,2,28,1,3,33,12,162,221,0,137,156
Typ=231 Len=11: 120,110,2,27,20,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-2010 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 2828-FEB-2010 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/Eastern. Now, 16:02:32 US/Pacific is 19:02:32 US/Eastern, so that is what was stored in the bytes …20,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:
ops$tkyte%ORA11GR2> select ts1, ts2 from t;
TS1
---------------------------------------------------------------------------
TS2
---------------------------------------------------------------------------
27-FEB-10 04.02.32.212000 PM US/PACIFIC
27-FEB-10 08.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/Eastern time zone, so 16:02:32 US/Pacific on the way in is now displayed as 8:00 pm East Coast time on the way out. You might be wondering why it is 8pm and not 7pm (3 hours difference). That is because the date I stored was stored when ‘Standard’ time was in effect and when I retrieved it – the current time zone was in ‘Daylight Savings’ time – the extra hour is due to that (so if you see different results, it could well just be due to Standard versus Daylight Savings). If you rerun the example with dates all in the same exact time period (I ran this originally on April 12th, 2010) :
ops$tkyte%ORA11GR2> delete from t;
1 row deleted.
ops$tkyte%ORA11GR2> insert into t (dt, ts1, ts2)
2 values ( timestamp'2010-04-12 16:02:32.212 US/Pacific',
3 timestamp'2010-04-12 16:02:32.212 US/Pacific',
4 timestamp'2010-04-12 16:02:32.212 US/Pacific' );
1 row created.
ops$tkyte%ORA11GR2> select ts1, ts2 from t;
TS1
---------------------------------------------------------------------------
TS2
---------------------------------------------------------------------------
12-APR-10 04.02.32.212000 PM US/PACIFIC
12-APR-10 07.02.32.212000 PM
it works more like you might expect – a three hour difference between the two. I only point this out to drive home the fact that time zone ‘math’ is much more complex than it appears!
The TIMESTAMP WITH LOCAL TIME ZONE provides sufficient support for most applications, if you need not remember the source time zone, but only need a datatype that provides consistent worldwide handling of date/time types. Additionally, the TIMESTAMP(0) WITH LOCAL TIMEZONE provides you the equivalent of a DATE type with time zone support—it consumes 7 bytes of storage and the ability to have the dates stored “normalized” in UTC form.
One caveat with regard to the TIMESTAMP WITH LOCAL TIME ZONE type is that once you create tables with this column, you will find your database’s time zone is “frozen,” and you will not be able to change it:
ops$tkyte@ORA11GR2> alter database set time_zone = 'PST';
alter database set time_zone = 'PST'
*
ERROR at line 1:
ORA-30079: cannot alter database timezone when database has
TIMESTAMP WITH LOCAL TIME ZONE columns
ops$tkyte@ORA11GR2> !oerr ora 30079
30079, 00000, "cannot alter database timezone when database has
TIMESTAMP WITH LOCAL TIME ZONE columns"
// *Cause: An attempt was made to alter database timezone with
// TIMESTAMP WITH LOCAL TIME ZONE column in the database.
// *Action: Either do not alter database timezone or first drop all the
// TIMESTAMP WITH LOCAL TIME ZONE columns.
It should be obvious why, if you were to change the database’s time zone, you would have to rewrite every single table with a TIMESTAMP WITH LOCAL TIME ZONE—their current values would be wrong given the new time zone!
</quote>