TIMESTAMP WITH LOCAL TIME ZONE Type-Datatypes
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:
$ sqlplus eoda/foo@PDB1
create table t( dt date,ts1 timestamp with time zone,ts2 timestamp with local time zone); Table created.
SQL> insert into t (dt, ts1, ts2)values ( timestamp’2014-02-27 16:02:32.212 US/Pacific’, timestamp’2014-02-27 16:02:32.212 US/Pacific’, timestamp’2014-02-27 16:02:32.212 US/Pacific’ );1 row created.
SQL> select dbtimezone from dual;DBT—MST
Now, when we dump those values as follows:
SQL> 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:
SQL> 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 the 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.
Note You may get slightly different results if the date was stored when the Standard time zone was in effect and then retrieved when Daylight Savings time is in effect. The output in the prior example would show a two-hour difference instead of what you would intuitively think would be a one-hour difference. 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 TIME ZONE 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:
SQL> alter database set time_zone = ‘PST’; alter database set time_zone = ‘PST’ *ERROR at line 1:ORA-30079: cannot alter database timezone when database hasTIMESTAMP WITH LOCAL TIME ZONE columns
SQL> !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 because their current values would be wrong, given the new time zone!