dcsimg
 

Understanding the Odd Behavior for Timestamp with Time Zone Columns in Oracle

Monday Jul 22nd 2019 by David Fitzjarrell

Timestamp with Oracle Time Zone columns exhibits 'interesting' behavior with respect to unique indexes and primary keys.  Read on to see the issue and one possibly confusing way to handle it!

Timestamps in Oracle can have one of three configurations: timestamp, timestamp with timezone and timestamp with local timezone. At first glance timestamp with timezone and timestamp with local timezone would seem to be the same, and they can be. They can also be different based on what timezone the current session is set to use. Basically, timestamp with timezone uses the timesone or timezone offset of the server where the database is running, and timestamp with local timezone uses the session timezone or timezone offset. For local sessions the timestamp values for both configurations will be the same, however for remote sessions in another timezone using timestamp with local timezone will use the timezone of the remote session. For example, if a user in Los Angeles, CA inserts data into the customer table in Duluth, MN and the timestamp column uses the local timezone offset the timestamp value will reflect California, not Minnesota, time. And because of this timestamp with timezone columns behave differently than timestamp with local timezone columns with respect to unique constraints and primary keys. Let's look at an example and see what Oracle does.

A table is created containing two columns defined as follows:


SPLEEBO @ umpqua > create table tz_tst_tbl (
  2  	     ts_w_tzone   timestamp(9) with time zone,
  3  	     ts_w_ltzone  timestamp(9) with local time zone
  4  );

Table created.

SPLEEBO @ umpqua > 

Data is inserted into both columns:


SPLEEBO @ umpqua > insert into tz_tst_tbl values(systimestamp, systimestamp);

1 row created.

SPLEEBO @ umpqua > commit;

Commit complete.

SPLEEBO @ umpqua > 

Now create unique constraints for both columns:


SPLEEBO @ umpqua > alter table tz_tst_tbl add constraint ts_w_ltzone_us unique (ts_w_ltzone);

Table altered.

So far, so good, the column with the local timezone is now unique. What about the column with the system timezone?


SPLEEBO @ umpqua > alter table tz_tst_tbl add constraint ts_w_tzone_us  unique (ts_w_tzone);
alter table tz_tst_tbl add constraint ts_w_tzone_us  unique (ts_w_tzone)
                                                     *
ERROR at line 1:
ORA-02329: column of datatype TIME/TIMESTAMP WITH TIME ZONE cannot be unique or a primary key 


SPLEEBO @ umpqua > 

An interesting turn of events. To make this even more interesting:


SPLEEBO @ umpqua > create unique index ts_w_tzone_us on tz_tst_tbl(ts_w_tzone);

Index created.

SPLEEBO @ umpqua > 
SPLEEBO @ umpqua > select index_name, column_name from user_ind_columns where table_name = 'TZ_TST_TBL';

INDEX_NAME                          COLUMN_NAME                                                                                     
----------------------------------- -----------------------------------                                                             
TS_W_LTZONE_US                      TS_W_LTZONE                                                                                     
TS_W_TZONE_US                       SYS_NC00003$                                                                                    

SPLEEBO @ umpqua > 

It appears that using the server timezone offset is 'off limits' with a unique constraint or primary key, but perfectly acceptable for a unique index. Proving there are two unique indexes against this table:


SPLEEBO @ umpqua > select * from user_ind_expressions where table_name = 'TZ_TST_TBL';

INDEX_NAME                          TABLE_NAME                          COLUMN_EXPRESSION                   COLUMN_POSITION         
----------------------------------- ----------------------------------- ----------------------------------- ---------------         
TS_W_TZONE_US                       TZ_TST_TBL                          SYS_EXTRACT_UTC("TS_W_TZONE")                     1         

SPLEEBO @ umpqua > 

Having a unique index on the timestamp with time zone column doesn't allow the unique constraint to be created:


SPLEEBO @ umpqua > alter table tz_tst_tbl add constraint ts_w_tzone_us  unique (ts_w_tzone) using index ts_w_tzone_us;
alter table tz_tst_tbl add constraint ts_w_tzone_us  unique (ts_w_tzone) using index ts_w_tzone_us
                                                     *
ERROR at line 1:
ORA-02329: column of datatype TIME/TIMESTAMP WITH TIME ZONE cannot be unique or a primary key


SPLEEBO @ umpqua >

To possibly understand this the original table is dropped and a new table with all three timestamp configurations is created:


SPLEEBO @ umpqua > drop table tz_tst_tbl purge;

Table dropped.

SPLEEBO @ umpqua > 
SPLEEBO @ umpqua > create table t1 (
  2  	     t0 	     timestamp,
  3  	     tz 	     timestamp with time zone,
  4  	     tl 	     timestamp with local time zone,
  5  	     ts_type	     varchar2(20)
  6  )
  7  ;

Table created.

SPLEEBO @ umpqua > 
SPLEEBO @ umpqua > insert into t1 values(
  2  	     systimestamp, systimestamp, systimestamp,
  3  	     'sys Timestamp'
  4  );

1 row created.

SPLEEBO @ umpqua > 
SPLEEBO @ umpqua > commit;

Commit complete.

SPLEEBO @ umpqua > 

Looking at the data the confusion remains:


SPLEEBO @ umpqua > select * from t1;

T0                                                                                                                                  
---------------------------------------------------------------------------                                                         
TZ                                                                                                                                  
---------------------------------------------------------------------------                                                         
TL                                                                          TS_TYPE                                                 
--------------------------------------------------------------------------- --------------------                                    
17-JUN-19 09.54.51.226695 AM                                                                                                        
17-JUN-19 09.54.51.226695 AM -06:00                                                                                                 
17-JUN-19 09.54.51.226695 AM                                                sys Timestamp                                           
                                                                                                                                    

SPLEEBO @ umpqua > 

Dumping the column types reveals a difference in the type definition for the timestamp with time zone column:


SPLEEBO @ umpqua > select
  2  	     dump(t0,16),
  3  	     dump(tz,16),
  4  	     dump(tl,16),
  5  	     ts_type
  6  from
  7  	     t1
  8  ;

DUMP(T0,16)                                                                                                                         
--------------------------------------------------------------------------
DUMP(TZ,16)                                                                                                                         
--------------------------------------------------------------------------
DUMP(TL,16)                                                                                                                         
--------------------------------------------------------------------------
TS_TYPE                                                                                                                             
--------------------                                                                                                                
Typ=180 Len=11: 78,77,6,11,a,37,34,d,83,17,58                                                                                       
Typ=181 Len=13: 78,77,6,11,10,37,34,d,83,17,58,e,3c                                                                                 
Typ=231 Len=11: 78,77,6,11,a,37,34,d,83,17,58                                                                                       
sys Timestamp                                                                                                                       
                                                                                                                                    

SPLEEBO @ umpqua > 

Values for timestamp and timestamp with local time zone columns are normalized to the database timezone; values for timestamp with time zone columns do not undergo that normalization. The error description for an ORA-02329 is:


02329, 00000, "column of datatype %s cannot be unique or a primary key"
// *Cause:   An attempt was made to place a UNIQUE or a PRIMARY KEY constraint
//           on a column of datatype VARRAY, nested table, object, LOB, FILE
//           or REF.
// *Action:  Change the column datatype or remove the constraint. Then retry
//           the operation.

so declaring a column as timestamp with time zone appears to classify this as an object type, a type not allowed in a primary key or unique constraint [none of the other types listed in the error explanation are "sensible" choices]. Notice that the data type definitions for timestamp and timestamp with local time zone appear to be the same (excluding internal functional differences) which is likely why timestamp and timestamp with local timezone types can be part of a unique constraint or a primary key.

Sometimes the most confusing behavior can have a simple explanation, if the time is taken to perform a suitable investigation. It may take some digging but, more often than not, the answer is there to be found.

# # #

See all articles by David Fitzjarrell

Home
Mobile Site | Full Site