dcsimg
Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
SQL Scripts & Samples
Tips
» Database Forum
» Slideshows
Free Newsletters:



News Via RSS Feed


Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

Oracle

Posted July 22, 2019

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

By David Fitzjarrell

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



Oracle Archives




Latest Forum Threads
Oracle Forum
Topic By Replies Updated
Oracle Data Mining: Classification jan.hasller 0 July 5th, 07:19 AM
Find duplicates - Unique IDs Lava 5 July 2nd, 08:30 AM
no matching unique or primary key rcanter 1 April 25th, 12:32 PM
Update values of one table based on condition of values in other table using Trigger Gladiator 3 February 29th, 06:01 PM