CURRENT_TIMESTAMP AND CURRENT USER as Defaults on a Column

[1dg618]

In Sybase 15, can I do the following:

CREATE TABLE my_table_name
(
updated_by VARCHAR(128) DEFAULT CURRENT
USER,
updated_date TIMESTAMP DEFAULT CURRENT
TIMESTAMP
)


where on an update the updated_by and updated_date will automatically
be updated? This way I don't have to use an update trigger to update
the columns?

My syntax may be wrong. I have never done it before.

Does this work in Sybase 15?

[Rob V]

'current user' and 'current timeastamp' are features that work in SQL
Anywhere and Sybase IQ,not in ASE.
In ASE, use either of the following:

- default user_name() -- if you want the name of the ASE database user
- default user -- same as user_name()
- default suser_name() -- if you want the name of the ASE login

- default getdate() -- get current date/time

BTW, the 'timestamp' datatype for that column is not what you think it
is. In SQL Anywhere/IQ this is a date/time datatype, but in ASE it has
nothign to do with time, despite the name. Instead it is an internally
generated unqiue number that can only be used in some very specific
application scenarios. SO don't use it -- use 'datetime' or
'bigdatetime' instead.

--
HTH,

Rob V.
-----------------------------------------------------------------
Rob Verschoor

Certified Sybase Professional DBA for ASE 15.0/12.5/12.0/11.5/11.0
and Replication Server 15.0.1/12.5 // TeamSybase

Author of Sybase books (order online at www.sypron.nl/shop):
"Tips, Tricks& Recipes for Sybase ASE" (ASE 15 edition)
"The Complete Sybase ASE Quick Reference Guide"
"The Complete Sybase Replication Server Quick Reference Guide"

r...@NO.SPAM.sypron.nl | www.sypron.nl | Twitter: @rob_verschoor
Sypron B.V., The Netherlands | Chamber of Commerce 27138666
-----------------------------------------------------------------

[Rob V]

On 18-Oct-2011 00:31, 1dg618 wrote:

[1dg618]

On Oct 18, 1:54 am, Rob V

> ------------------------------------------------------------------ Hide quoted text -
>
> - Show quoted text -

Thank you.

[Derek Asirvadem]

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