What's Wrong with Oracle TIMESTAMP WITH TIME ZONE Data-type?

First of all, let's start by recalling what a TIMESTAMP WITH TIME ZONE data-type is. According to the Oracle official documentation "a TIMESTAMP WITH TIME ZONE is a variant of TIMESTAMP that includes a time zone region name or time zone offset in its value. The time zone offset is the difference (in hours and minutes) between local time and UTC (Coordinated Universal Time, formerly Greenwich Mean Time)".

Well, very nice... this is a data-type which might be very useful in a distributed environment across all over the world where, unlike its counterpart TIMESTAMP WITH LOCAL TIME ZONE data-type, is also important to record the time zone from which, a transaction or whatsoever information has been inputed into the system. A potential suitable case for using this timezone information might be the producing of a report with all peak hours of activity in the context of a specific timezone area.

So far so good. What I really don't like about this data-type is that I cannot create a primary/unique constraint on a such a column. The Oracle documentation clear states that "none of the columns in the unique/primary key can be of LOB, LONG, LONG RAW, VARRAY, NESTED TABLE, OBJECT, REF, TIMESTAMP WITH TIME ZONE, or user-defined type. However, the unique/primary key can contain a column of TIMESTAMP WITH LOCAL TIME ZONE".

Furthermore, any index created for such a column is actually converted into a functional index. Behind the scenes, Oracle automatically normalize all the values of a TIMESTAMP WITH TIME ZONE column using the SYS_EXTRACT_UTC() function. In a way, this makes sens. Suppose we have to order the result-set of a SQL-SELECT using a TIMESTAMP WITH TIME ZONE column. Having for example:

TIMESTAMP '2003-01-01 2:00:00 -08:00
TIMESTAMP '2003-01-01 2:00:00 -02:00

what record should be the first one? According to what point of reference? The solution is to normalize all values and only after that to compare the values. Oracle transparently does this.
Now, what if I want that the values to be unique in a TIMESTAMP WITH TIME ZONE column? Well, you can't create a unique constraint but you can create a unique index.

SQL> create table muci_test (
  2   col1 timestamp with time zone,
  3   constraint uk_muci_test_col1 unique(col1)
  4  );

create table muci_test (
 col1 timestamp with time zone,
 constraint uk_muci_test_col1 unique(col1)
)

ORA-02329: column of datatype TIME/TIMESTAMP WITH TIME ZONE 
cannot be unique or a primary key
SQL> create table muci_test (
  2   col1 timestamp with time zone
  3  );

Table created

Executed in 0,204 seconds

SQL> create unique index ix_muci_text_col1 on muci_test(col1);

Index created

Executed in 0,25 seconds

SQL> insert into muci_test values (to_timestamp('22-MAR-08 11.25.39.457137'));

1 row inserted

Executed in 0,031 seconds

SQL> insert into muci_test values (to_timestamp('22-MAR-08 11.25.39.457137'));

insert into muci_test values (to_timestamp('22-MAR-08 11.25.39.457137'))

ORA-00001: unique constraint (ADMIN.IX_MUCI_TEXT_COL1) violated

As I already said, Oracle has actually created a functional index and this can be easily checked:

SQL> select index_type from user_indexes where index_name = 'IX_MUCI_TEXT_COL1';

INDEX_TYPE
---------------------------
FUNCTION-BASED NORMAL

If you don't feel very comfortable with functional indexes or you really want a primary/unique key constraint then the application design should take into consideration this limitation. Me personally, I would choose a TIMESTAMP WITH LOCAL TIME ZONE data-type and an additional VARCHAR2 column to store the originating TIME ZONE offsets. Using this approach it is possible to enforce a multi-column primary/unique key.