What is the mysterious 'timestamp' datatype in Sybase?

Question

I recently discovered a table in our Sybase database at work that uses a column of a type 'timestamp'. If I create a table using this mysterious timestamp datatype like this

create table dropme (
    foo timestamp,  
    roo int null
)
insert into dropme (roo) values(123)
insert into dropme (roo) values(122)
insert into dropme (roo) values(121)
select * from dropme
go

I get the following from 'select * from dropme':

 foo                  roo
 -------------------- -----------
   0x000100000e1ce4ea         123
   0x000100000e1ce4ed         122
   0x000100000e1ce509         121

0x000100000e1ce4ea does not look very timestampy to me. Also, I see this output from 'sp_help timestamp':

 Type_name Storage_type Length Prec Scale Nulls Default_name Rule_name Access_Rule_name Identity
 --------- ------------ ------ ---- ----- ----- ------------ --------- ---------------- ----------
 timestamp varbinary         8 NULL  NULL     1 NULL         NULL      NULL                   NULL

My questions are the following

1. What the heck is a timestamp?
2. Does it have any relation at all to time or date?
3. Can I convert it to a datetime?
4. If it's not a time or a date, what do you use it for?

Answer 1

What the heck is a timestamp?

The timestamp datatype is defined as

varbinary(8) null

Does it have any relation at all to time or date?

No. The name was poorly chosen.

Can I convert it to a datetime?

No.

If its not a time or a date, what do you use it for?

Each time a row with a timestamp column is inserted or updated, the timestamp column is updated automatically. Note that there are actually two kinds of timestamps. TIMESTAMP and CURRENT TIMESTAMP. The difference is that CURRENT TIMESTAMP is only set on insert.

The Sybase documentation stopped there leaving me wondering why the f*rainbow!*k anyone would ever use the datatype timestamp. Happily, I found some other discussions and deduced its used when implementing optimistic concurrency control.

Concurrency control is a method of ensuring that multiple transactions can run at/around the same time and still result in correct data. Optimistic concurrency control is a concurrency control method that assumes multiple transactions can complete without interfering with each other. Ie no locking is required. Wikipedia describes the following algorithm:

1. Record a date/time marking when the transaction starts
2. Read/update data
3. Check if another transaction modified the data
4. Commit or rollback

Sybase's timestamp datatype could be used in steps 1 and 3 of this algorithm instead of using a date/time. But it doesn't seem to me like it saves you much work over using a datetime datatype. I suppose it might perform better.

Answer 2

Recently, somebody asked me if it is possible to convert the TIMESTAMP SYBASE IQ data type to DATE; I have always avoided this data type because it's darkness. After some pair of hours of reading SYBASE documentation and making some tests, here are my conclusions:

The TIMESTAMP:

• Is a number of 12 digits, stored as BINARY (this could vary depending on the environment)
• It represents a value in micro-seconds since 1970 January 1st
• Sybase does not include direct functions for converting them
• It is stablished automatically every time a record is INSERTED

Here is the SQL sentence for converting the TIMESTAMP to DATE:

SELECT timestamp as TS, CONVERT(decimal, timestamp) as TS_IN_MS,
   CONVERT(date, dateadd(SS, CONVERT(int, SUBSTRING(CONVERT(varchar,                     
      CONVERT(decimal, timestamp)), 1, 9)), '1/1/1970'), 123)  as TS_AS_DATE  
   FROM TheTable

The conversion can be proved by using an online EPOCH converter like the following:

• http://www.fileformat.info/tip/java/date2millis.htm
• http://www.esqsoft.com/javascript_examples/date-to-epoch.htm

Note: In the case of SYBASE ASE, the TIMESTAMP type is not a valid UNIX-EPOCH.