Rob's definition of TIMESTAMP datatype is incorrect.
The Sybase ASE TIMESTAMP datatype is a true timestamp, as the name
states, and as per the Unix timestamp. The notion of the Unix
timestamp is a 32-bit integer containing seconds since the epoch, the
current timestamp is 1319056420 UTC. A value of clock ticks since
boot is also available. The values are supplied to ASE by the Unix
time() and times() functions. The ASE TIMESTAMP is 64-bit integer,
but it is stored in internal form. The content is clock ticks since
the epoch (don't quote me on that *particular* detail, it has been a
few years since I wrote code to extract it). WHich means, it has far
more precision (clock ticks) than DATETIME (3 milliseconds) There is
nothing random about it, it is serial, and uniqueness does not apply
(although not intended, in some cases it may be a valid, but silly,
key).
The Sybase ASE TIMESTAMP datatype is a true timestamp:
- its purpose is to provide application level Optimistic Locking
- it is not random, it is absolutely a time value (but not accessible)
- which can be relied upon
- to be used by the TSEQUAL() function
- it is automatically updated upon INSERT/UPDATE
- it cannot be SET during UPDATE or INSERT
- it is protected (cannot by subverted, hence the inaccessibility)
- completely visible via SELECT, as a BINARY hex value
- can be used to compare (if you don't like TSEQUAL or whatever)
- fully integrated with OpenClient Lib ("browse")
That was it, in the days of the 32-bit limit: 32-bit DATETIME with 3
millisecs or 64-bit TIMESTAMP with clock ticks. Obviously excellent
if you use it for that purpose, or you neded more precision than 3
millisecs. However, if you want a more "user-friendly" timestamp, and
you are not using TSEQUAL() for your Optimistic Locking, sure, use
DATETIME.
Now that we are in the 64-bit era, and BIGDATETIME with microseconds
is available, TIMESTAMP is positioned to become a relic when the
thousands of systems that use it get decommissioned.
Regards
Derek