Path: utzoo!utgpu!news-server.csri.toronto.edu!cs.utexas.edu!ubiquity
From: ubiquity@cs.utexas.edu (Richard Hoffman)
Newsgroups: comp.databases
Subject: Re: Wondering about using datetime as unique key...
Message-ID: <17257@cs.utexas.edu>
Date: 25 Jan 91 13:37:40 GMT
References: <48429@apple.Apple.COM>
Organization: Ubiquitous Productions, R. Hoffman Proprietor
Lines: 42

In article <48429@apple.Apple.COM> dshapiro@Apple.COM (David Shapiro) writes:

>I haven't seen anyone talk about using the timestamp as a unique
>key to table. 

This is frequently done on systems where (as you note later) the
timestamp has sufficient granularity.  In SAA relational databases,
timestamp goes into the microseconds, which is more than enough to
guarantee uniqueness.

The value is always available via CURRENT TIMESTAMP, and can be added
to inserted rows automatically if a DEFAULT clause (either the DB2 NOT NULL
WITH DEFAULT or the ANS/ISO column default) is available.

One difficulty is with multi-row updates, or inserts from a subselect.
SAA SQL requires that the value of things such as CURRENT TIMESTAMP be
calculated at the beginning of the operation, and so would be the same
for all rows in a multi-row operation.  Of course, a unique index or
PRIMARY KEY clause would catch this, but it is inconvenient.

>Possible duplication if someone screws with the system time.
>(no one can be using the system during the daylight savings time adjustment)

Yes, one of the amusing things about IMS is that it must be down for
one hour out of each year while the clocks are being set back.  In a
relational system, there is less of a problem: put a unique index or
PRIMARY KEY clause on the column, and check after each insertion to
make sure that the row is not rejected because of the uniqueness
constraint.  If it is, simply try again.  You'll eventually get a
unique one (probably first or second try, with microsecond granularity).

>Portability to new platform could require some time manipulations.

Too true, especially since date/time features are not standard yet.
But the approval of SQL2, expected this year, could ease that.


-- 
Richard Hoffman       IBM Personal Systems Database Development   (512) 823-1822
1529 Ben Crenshaw Way
Austin, TX 78746         "Life is a gamble at terrible odds; 
(512) 327-9232            if it were a bet you wouldn't take it"  (Tom Stoppard)