Sybase NNTP forums - End Of Life (EOL)

The NNTP forums from Sybase - forums.sybase.com - are now closed.

All new questions should be directed to the appropriate forum at the SAP Community Network (SCN).

Individual products have links to the respective forums on SCN, or you can go to SCN and search for your product in the search box (upper right corner) to find your specific developer center.

how to use timestamp

5 posts in General Discussion Last posting was on 2010-03-04 03:03:13.0Z
Ray Posted on 2010-03-03 16:28:21.0Z
From: "Ray" <lcm@hotmail.com>
Newsgroups: sybase.public.ase.general
Subject: how to use timestamp
Lines: 12
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.5843
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.5579
X-RFC2646: Format=Flowed; Original
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4b8e8e25@forums-1-dub>
Date: 3 Mar 2010 08:28:21 -0800
X-Trace: forums-1-dub 1267633701 10.22.241.152 (3 Mar 2010 08:28:21 -0800)
X-Original-Trace: 3 Mar 2010 08:28:21 -0800, vip152.sybase.com
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:29014
Article PK: 78252

Hi,

I figure out there is a timestamp datatype in Sybase. If I want to know what
is the latest time
a row is updated? Can timestamp do this purpose?

If yes, how to show the value of timestamp in SQL query?

Thanks,
Raymond


John McVicker Posted on 2010-03-03 16:37:19.0Z
From: "John McVicker" <jmcvicker@inventa.com>
Newsgroups: sybase.public.ase.general
References: <4b8e8e25@forums-1-dub>
In-Reply-To: <4b8e8e25@forums-1-dub>
Subject: Re: how to use timestamp
Lines: 43
MIME-Version: 1.0
Content-Type: text/plain; format=flowed; charset="iso-8859-1"; reply-type=response
Content-Transfer-Encoding: 7bit
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Windows Mail 6.0.6001.18000
X-MimeOLE: Produced By Microsoft MimeOLE V6.0.6001.18000
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4b8e903f$1@forums-1-dub>
Date: 3 Mar 2010 08:37:19 -0800
X-Trace: forums-1-dub 1267634239 10.22.241.152 (3 Mar 2010 08:37:19 -0800)
X-Original-Trace: 3 Mar 2010 08:37:19 -0800, vip152.sybase.com
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:29015
Article PK: 78255

Timestamp is not used for "date or time" but it is a constantly increasing
counter within the server. It is used for optimistic concurrency.

I would say use smalldatetime and datetime datatypes as many customers do.
They tend to do the update through a trigger to automatically update this
datetime column to be getdate() upon update.

For instance:

create table MyTable
(Your columns....,
pk_column numeric(18,0) identity -- unique column(s) to identify a
single row
late_updated smalldatetime)

create trigger MyTable_Trig_IU
for insert, update
on MyTable
as
update MyTable
set last_updated = getdate() -- some customers also capture the suid
or suser_name() as well.
where MyTable.pk_column = inserted.pk_column

Other customers write the old row into an archive table to store the entire
old row for auditing purposes.

- John

"Ray" <lcm@hotmail.com> wrote in message news:4b8e8e25@forums-1-dub...
> Hi,
>
> I figure out there is a timestamp datatype in Sybase. If I want to know
> what is the latest time
> a row is updated? Can timestamp do this purpose?
>
> If yes, how to show the value of timestamp in SQL query?
>
> Thanks,
> Raymond
>


"Mark A. Parsons" <iron_horse Posted on 2010-03-03 18:07:53.0Z
From: "Mark A. Parsons" <iron_horse@no_spamola.compuserve.com>
User-Agent: Thunderbird 1.5.0.10 (Windows/20070221)
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: tracking last modified time (and user)
References: <4b8e8e25@forums-1-dub> <4b8e903f$1@forums-1-dub>
In-Reply-To: <4b8e903f$1@forums-1-dub>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
X-Antivirus: avast! (VPS 100301-0, 03/01/2010), Outbound message
X-Antivirus-Status: Clean
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4b8ea579$1@forums-1-dub>
Date: 3 Mar 2010 10:07:53 -0800
X-Trace: forums-1-dub 1267639673 10.22.241.152 (3 Mar 2010 10:07:53 -0800)
X-Original-Trace: 3 Mar 2010 10:07:53 -0800, vip152.sybase.com
Lines: 113
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:29016
Article PK: 78253

If using ASE 15.x, computed columns can be used to keep last modified date (and user) up to date without the use of a
trigger.

NOTE: Triggers are still needed if copying data to a different table, eg, moving old records to an archive table.

-------------

A few key issues to keep in mind:

- the computed column will only get updated if its definition references a column that has been modified

- may need to do a little odd-ball function manipulation of the referenced column in order to insure you generate a
value of the correct datatype

- make sure the computed column is 'materialized'

- if you need to worry about multiple columns being updated, consider adding a timestamp column to the table; the
timestamp column is updated whenever any other column in the table is modified, so it becomes a lot easier to write a
computed column defintion against a single timestamp column

-------------

As an example of using computed columns to keep track of the last modification date and user ...

===============================
create table bogus_data
(id int
,name varchar(30)
,address varchar(100)
,ts timestamp
,mod_date as dateadd(ss,0*ts,getdate()) materialized
,mod_user as user_name()+left('',0*ts) materialized
)
go
===============================

Any time the id, name or address columns are updated ... the ts column will be updated ... and the changing ts column
value insures the 2 computed columns are updated, too.

-------------

Some explanation about the computed column definitions ...

- each definition accesses the timestamp column (ts) to insure the computed column is updated whenever any columns in
the table are modified

- we don't care about the actual value of the ts column, so we multiple by 0 to insure we always have a 0 value to work
with; we'll call this zero-masking the ts value

- the dateadd() and left() function calls are bogus function calls that allow us to reference the zero-masked ts value
in such a way that the resulting value does not affect the *real* data that we're after, namely the getdate() and
user_name() function calls

- use ASE pre-defined builtin functions whenever possible; user-defined functions (SQL UDFs) incur an overhead to call
(similar to the overhead of calling a stored proc or trigger)

- the odd-ball function calls (dateadd() and left() in this example) should generate a NULL/zero result that is
compatible with the datatype of the value we're actually looking to store in this colum

-------------

While the computed column definitions can be a bit awkward to get used to at first, the performance overhead is
typically less than that of a trigger, while also freeing up room in procedure cache (ie, no need to compile/load a
trigger into proc cache).

John McVicker wrote:
> Timestamp is not used for "date or time" but it is a constantly
> increasing counter within the server. It is used for optimistic
> concurrency.
>
> I would say use smalldatetime and datetime datatypes as many customers
> do. They tend to do the update through a trigger to automatically update
> this datetime column to be getdate() upon update.
>
> For instance:
>
> create table MyTable
> (Your columns....,
> pk_column numeric(18,0) identity -- unique column(s) to identify a
> single row
> late_updated smalldatetime)
>
> create trigger MyTable_Trig_IU
> for insert, update
> on MyTable
> as
> update MyTable
> set last_updated = getdate() -- some customers also capture the
> suid or suser_name() as well.
> where MyTable.pk_column = inserted.pk_column
>
> Other customers write the old row into an archive table to store the
> entire old row for auditing purposes.
>
> - John
>
>
> "Ray" <lcm@hotmail.com> wrote in message news:4b8e8e25@forums-1-dub...
>> Hi,
>>
>> I figure out there is a timestamp datatype in Sybase. If I want to
>> know what is the latest time
>> a row is updated? Can timestamp do this purpose?
>>
>> If yes, how to show the value of timestamp in SQL query?
>>
>> Thanks,
>> Raymond
>>
>


Sherlock, Kevin [TeamSybase] Posted on 2010-03-03 20:31:53.0Z
From: "Sherlock, Kevin [TeamSybase]" <kevin.sherlock@teamsybase.com>
Newsgroups: sybase.public.ase.general
References: <4b8e8e25@forums-1-dub> <4b8e903f$1@forums-1-dub> <4b8ea579$1@forums-1-dub>
Subject: Re: tracking last modified time (and user)
Lines: 155
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.3138
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.3198
X-RFC2646: Format=Flowed; Response
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4b8ec739@forums-1-dub>
Date: 3 Mar 2010 12:31:53 -0800
X-Trace: forums-1-dub 1267648313 10.22.241.152 (3 Mar 2010 12:31:53 -0800)
X-Original-Trace: 3 Mar 2010 12:31:53 -0800, vip152.sybase.com
X-Authenticated-User: teamsybase
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:29017
Article PK: 78254

One could avoid the timestamp column by using the "hashbytes" builtin to do
kind of a "checksum" of the row. In the matter of illustrating an
interesting alternative:

create table bogus_data
(id int
,name varchar(30)
,address varchar(100)
,mod_date as dateadd(ss,0* hashbytes('sha',id,name,address),getdate())
materialized
,mod_user as user_name()+left('',0* hashbytes('sha',id,name,address))
materialized
)
go

saves 8 bytes of storage per row. drawback is that if you add columns to
the table after the fact, you must maintain this materialized column.

I would also note that no solution addresses something like:

alter table bogus_data add status varchar(10) default 'new'

Conceptually, you just updated each row, but neither solution (nor a
trigger) will reflect an updated mod_date until you actually use and
update/insert statement on the table.

"Mark A. Parsons" <iron_horse@no_spamola.compuserve.com> wrote in message
news:4b8ea579$1@forums-1-dub...
> If using ASE 15.x, computed columns can be used to keep last modified date
> (and user) up to date without the use of a trigger.
>
> NOTE: Triggers are still needed if copying data to a different table, eg,
> moving old records to an archive table.
>
> -------------
>
> A few key issues to keep in mind:
>
> - the computed column will only get updated if its definition references a
> column that has been modified
>
> - may need to do a little odd-ball function manipulation of the referenced
> column in order to insure you generate a value of the correct datatype
>
> - make sure the computed column is 'materialized'
>
> - if you need to worry about multiple columns being updated, consider
> adding a timestamp column to the table; the timestamp column is updated
> whenever any other column in the table is modified, so it becomes a lot
> easier to write a computed column defintion against a single timestamp
> column
>
> -------------
>
> As an example of using computed columns to keep track of the last
> modification date and user ...
>
> ===============================
> create table bogus_data
> (id int
> ,name varchar(30)
> ,address varchar(100)
> ,ts timestamp
> ,mod_date as dateadd(ss,0*ts,getdate()) materialized
> ,mod_user as user_name()+left('',0*ts) materialized
> )
> go
> ===============================
>
> Any time the id, name or address columns are updated ... the ts column
> will be updated ... and the changing ts column value insures the 2
> computed columns are updated, too.
>
> -------------
>
> Some explanation about the computed column definitions ...
>
> - each definition accesses the timestamp column (ts) to insure the
> computed column is updated whenever any columns in the table are modified
>
> - we don't care about the actual value of the ts column, so we multiple by
> 0 to insure we always have a 0 value to work with; we'll call this
> zero-masking the ts value
>
> - the dateadd() and left() function calls are bogus function calls that
> allow us to reference the zero-masked ts value in such a way that the
> resulting value does not affect the *real* data that we're after, namely
> the getdate() and user_name() function calls
>
> - use ASE pre-defined builtin functions whenever possible; user-defined
> functions (SQL UDFs) incur an overhead to call (similar to the overhead of
> calling a stored proc or trigger)
>
> - the odd-ball function calls (dateadd() and left() in this example)
> should generate a NULL/zero result that is compatible with the datatype of
> the value we're actually looking to store in this colum
>
> -------------
>
> While the computed column definitions can be a bit awkward to get used to
> at first, the performance overhead is typically less than that of a
> trigger, while also freeing up room in procedure cache (ie, no need to
> compile/load a trigger into proc cache).
>
>
>
>
> John McVicker wrote:
>> Timestamp is not used for "date or time" but it is a constantly
>> increasing counter within the server. It is used for optimistic
>> concurrency.
>>
>> I would say use smalldatetime and datetime datatypes as many customers
>> do. They tend to do the update through a trigger to automatically update
>> this datetime column to be getdate() upon update.
>>
>> For instance:
>>
>> create table MyTable
>> (Your columns....,
>> pk_column numeric(18,0) identity -- unique column(s) to identify a
>> single row
>> late_updated smalldatetime)
>>
>> create trigger MyTable_Trig_IU
>> for insert, update
>> on MyTable
>> as
>> update MyTable
>> set last_updated = getdate() -- some customers also capture the suid
>> or suser_name() as well.
>> where MyTable.pk_column = inserted.pk_column
>>
>> Other customers write the old row into an archive table to store the
>> entire old row for auditing purposes.
>>
>> - John
>>
>>
>> "Ray" <lcm@hotmail.com> wrote in message news:4b8e8e25@forums-1-dub...
>>> Hi,
>>>
>>> I figure out there is a timestamp datatype in Sybase. If I want to know
>>> what is the latest time
>>> a row is updated? Can timestamp do this purpose?
>>>
>>> If yes, how to show the value of timestamp in SQL query?
>>>
>>> Thanks,
>>> Raymond
>>>
>>


Ray Posted on 2010-03-04 03:03:13.0Z
From: "Ray" <lcm@hotmail.com>
Newsgroups: sybase.public.ase.general
References: <4b8e8e25@forums-1-dub>
Subject: Re: how to use timestamp
Lines: 18
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.5843
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.5579
X-RFC2646: Format=Flowed; Response
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4b8f22f1@forums-1-dub>
Date: 3 Mar 2010 19:03:13 -0800
X-Trace: forums-1-dub 1267671793 10.22.241.152 (3 Mar 2010 19:03:13 -0800)
X-Original-Trace: 3 Mar 2010 19:03:13 -0800, vip152.sybase.com
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:29021
Article PK: 78258

Thanks for your guys information. It is really useful.

Thanks.

"Ray" <lcm@hotmail.com> wrote in message news:4b8e8e25@forums-1-dub...
> Hi,
>
> I figure out there is a timestamp datatype in Sybase. If I want to know
> what is the latest time
> a row is updated? Can timestamp do this purpose?
>
> If yes, how to show the value of timestamp in SQL query?
>
> Thanks,
> Raymond
>