Oracle Scratchpad

May 29, 2019

Timestamp Oddity

Filed under: Function based indexes,Indexing,Oracle — Jonathan Lewis @ 6:17 pm BST May 29,2019

[Editorial note: this is something I started writing in 2013, managed to complete in 2017, and still failed to publish. It should have been a follow-on to another posting on the oddities of timestamp manipulation.]

Just as national language support used to be, timestamps and time-related columns are still a bit of a puzzle to the Oracle world – so much so that OEM could cripple a system if it was allowed to do the check for “failed logins over the last 30 minutes”. And, just like NLS, it’s one of those things that you use so rarely that you keep forgetting what went wrong the last time you used it. Here’s one little oddity that I reminded myself about recently:

rem     Script:         timestamp_anomaly.sql
rem     Author:         Jonathan Lewis
rem     Dated:          April 2013
create table t1 (
        ts_tz   timestamp(9) with time zone,
        ts_ltz  timestamp(9) with local time zone

insert into t1 values(systimestamp, systimestamp);

alter table t1 add constraint ts_ltz_uk unique (ts_ltz);
alter table t1 add constraint ts_tz_uk  unique (ts_tz);

Nothing terribly difficult – just a table with two variants on the timestamp data type and a unique constraint on both: except for one problem. Watch what happens as I create the unique constraints:

SQL> alter table t1 add constraint ts_ltz_uk unique (ts_ltz);

Table altered.

SQL> alter table t1 add constraint ts_tz_uk  unique (ts_tz);
alter table t1 add constraint ts_tz_uk  unique (ts_tz)
ERROR at line 1:
ORA-02329: column of datatype TIME/TIMESTAMP WITH TIME ZONE cannot be unique or a primary key

Slightly unexpected – unless you’ve memorized the manuals, of course, which I hadn’t. I wonder if you can create a unique index on timestamp with time zone:

SQL> create unique index ts_tz_uk on t1(ts_tz);

Index created.

You can’t have a unique constraint, but you CAN create a unique index! How curious – did that really happen ?

SQL> select index_name, column_name from user_ind_columns where table_name = 'T1';

-------------------- --------------------
TS_LTZ_UK            TS_LTZ
TS_TZ_UK             SYS_NC00003$

The index is on a column called SYS_NC00003$ – which looks suspiciously like one of those “function-based-index” things:

SQL> select * from user_ind_expressions where table_name = 'T1';

INDEX_NAME           TABLE_NAME           COLUMN_EXPRESSION                        COLUMN_POSITION
-------------------- -------------------- ---------------------------------------- ---------------
TS_TZ_UK             T1                   SYS_EXTRACT_UTC("TS_TZ")                               1

Oracle has silently invoked the sys_extract_utc() function on our (free-floating) timestamp column to normalize it to UTC. This is really not very friendly but it does make sense, of course – it would be rather expensive to enforce uniqueness if there were (at least) 24 different ways of storing the same absolute value – and 24 is a conservative estimate.




  1. Which, then, begs the question – why doesn’t Oracle allow unique constraints on timestamp-with-time-zone columns? With the same semantics as the unique index. Oracle enforces unique constraints via indexes; I don’t see what other obstacle there is. Were indexes on timestamp-with-time-zone columns (using SYS_EXTRACT_UTC in the process) added to the database only in recent versions – and somehow no one thought to allow unique constraints at that same time?

    Regards – mathguy

    Comment by mathguy — February 12, 2021 @ 10:28 pm GMT Feb 12,2021 | Reply

    • mathguy,

      Thanks for the comment.

      I can’t think of any obvious good reason either, but it seems a little unlikely that someone coding for unique indexes was accidentally overlook the possibility of unique constraints using the same strategy so there’s probably something to do with complexity or side-effects somewhere. The only idea I can come up with at the moment is that there may be some special code relating to referential integrity that was “temporarily postponed” and then never revisited but that doesn’t sound likely.

      Jonathan Lewis

      Comment by Jonathan Lewis — February 15, 2021 @ 6:02 pm GMT Feb 15,2021 | Reply

      • Hi folks,

        Daylight saving time (DST) rules can become a headache. I remember I was patching my databases two years in a row because the government couldn’t decide what offset they should use.
        See for example: Asia/Novosibirsk (Russia) switch from +06 to +07 on July 2016 – Impact on Oracle RDBMS (Doc ID 2152421.1)

        SYS_EXTRACT_UTC might be viable for dates in the past, but it is not the case with the future. Even for the past dates there are some caveats:
        > Oracle time zone data may not reflect the most recent data available at this site.

        Some shops do not apply timezone patches at all, and rely on timezone files that were supplied with the Oracle Home.


        Comment by Mikhail Velikikh — February 15, 2021 @ 8:07 pm GMT Feb 15,2021 | Reply

  2. SYS_EXTRACT_UTC(“TS_TZ”) – that’s a NON-DETERMINISTIC function, we’re seeing ORA-08102 errors like described here ->

    There is something not quite right with indexing a TIMESTAMP WITH TIME ZONE

    Comment by Ori — November 17, 2021 @ 7:46 pm GMT Nov 17,2021 | Reply

    • Ori,

      Thanks for the comment.

      The problem with the timestamp with timezone is that in principle the function to convert to UTC ought to be deterministic. Admittedly it’s difficult to work out exactly what the UTC time might be thanks to the constant change in the timezone data files. Perhaps the theoretical determinism of the function explains why it has not been flagged by Oracle as non-deterministic; however, as it stands, the following is value:

      create index t1_i2 on t1(sys_extract_utc(ts_tz))

      and doesn’t raise the error:

      ORA-30553: The function is not deterministic

      Jonathan Lewis

      Comment by Jonathan Lewis — November 18, 2021 @ 2:39 pm GMT Nov 18,2021 | Reply

RSS feed for comments on this post. TrackBack URI

Comments and related questions are welcome.

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Website Powered by

%d bloggers like this: