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?

Abstention

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.

Abstention

The resolution of datetime is not that large it id relatively easy to get inserts giving two rows with the same datetime – Towrope

Wiki is pathetic. The Timestamp is designed for Optimistic Locking not OCC, which existed before the amateurs who wrote wiki. The sequence give above is incorrect. The other link is better but does not explain OL. If you open a new question "What is Optimistic Locking", I will answer it. – Trihedron

@Mark. True. The resolution of DATETIME is 3 milliseconds. But your point does not apply here. In Optimistic Locking (again badly explained in this answer) what is relevant is the difference between the row in Client memory (app; stored proc; batch; etc) and the row that is in the Server, which indicates that the row in the Server has been updated since the fetch. It is indeed standard practice to use a DATETIME column for a readable timestamp. – Trihedron

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.

Eleanoreleanora

You answer is good, except for one thing: the statement re Sybase ASE is false. Look up the Unix Epoch (link in my Answer). In your code, you add the Unix Epoch [1/1/1970], in seconds, to the IQ TIMESTAMP, converted to seconds. It works. In IQ. Good. The Sybase ASE TIMESTAMP is in milliseconds. If you write the same SQL, for ASE, with that in mind, it will work. Try it. The statement In the case of SYBASE ASE, the TIMESTAMP type is not a valid UNIX-EPOCH." is false. – Trihedron

Answer 3

(This is an answer posted as a separate question Answers to the mysterious Sybase ASE 'timestamp' datatype questions by a user without the rep to add it here. I've copied it over as Community Wiki since I don't want to claim credit for it but it should be here)

Answer to Q#1 : 'What the heck is timestamp?'

• The timestamp of a Sybase ASE database is held centrally in an internal in-memory table 'dbtable' of that database - this table is built when a database is brought online. You can query the current DB timestamp by select @@dbts - please be aware that this varbinary(8) 'Database' timestamp value is platform dependent i.e. subjected to Big versus Small endianness.

• Each user table may have one timestamp column for holding the 'Database' timestamp values of the INSERT / UPDATE of a given row. All 'Table' timestamp column values are automatically maintained by ASE (just like identity column) at the successful completion of a TSQL DML command. However, unlike the 'Database' timestsamp, the 'Table' timestamp values are platform independent as they are always preserved in Big-endian byte-order regardless of the O/S platform's endianness (see further information below for details).

---

Answer to Q#2 : 'Does it have any relation at all to time or date?'

No, the values in the 'Database' timestamp and page 'Local' timestamps do not reflect the actual date/time.

---

Answer to Q#3 : 'Can I convert it to a datetime?'

No, you cannot convert 'Database' timestamp or 'Local' timestamps of its pages to date/time value.

---

Answer to Q#4 : 'If its not a time or a date, what do you use it for?'

• The 'Database' timestamp is incremented by one whenever a page within a database is modified or created while the affected page's 'Local' timestamp (within its page header) is then synchronised with the 'Database' timestamp of that point in time.

• When compared with the 'Database' timestamp at the the present time, a database page's 'Local' timestamp reflects the relative age of that page's last update or first create; hence, ASE can tell the chronological order of updates/creates to all pages within a database.

• Application can make use of the 'Table' timestamp column in similar fashion to identity column to find the most recently or least recently inserted/updated rows regardless of the key values of the rows.

---

Further information, warnings and caveats:-

(1) The 'Database' and 'Local' timestamps are stored in 3 parts and is OS platform endianness dependent. e.g. 0xHHHH 0000 LLLLLLLL

• 2-byte high-order - 0xHHHH
• 2-byte filler - 0x0000
• 4-byte low-order - 0xLLLLLLLL

(2) The user 'Table' timestamp is also stored in 3 parts but it is always in Big-endian orientation. e.g. 0x0000 HHHH LLLLLLLL

• 2-byte filler - 0x0000
• 2-byte high-order - 0xHHHH
• 4-byte low-order - 0xLLLLLLLL

(3) The database timestamp is held in a in-memory system table dbtable of a given database (, which is created when a database is brought on line).

• Note1 -'Table' timestamp column values are held just like other column values in the data and/or index pages of the database table, in which the timestamp column is defined.
• Note2 - Be aware that querying the current database's 'Database' timestamp by SELECT @@dbts returns its hex representation, which is subjected to the OS platform's Endianness.
• Note3 - In contrast, querying the 'Database' timestamp by DBCC dbtable (not recommended) returns its Big-endian hex representation, thus, it is platform independent.
• WARNING - When the 'Database' timestamp of a given database approaches its maximum limit i.e. (0xFFFF, 0xFFFFFFFF), and it may take a decade or more to reach this point depending on the frequencies of insert/update operations within the database, ASE will throw a warning and no further insert/update will be possible - the only option is to export the data from all objects using BCP (plus stored procedures via sp_showtext), drop the database, create it again (with new near-zer 'Database' timestamp) and import the data (and stored procedures).

FYI - The above answers, hints & tips are authentic and accurate as I worked for Sybase and now work SAP, who owns the product ASE.

Louth

Answer 4

Let's say you fetch the data to your application. After doing something you want to ensure that this record has been changed until you get (in low level!)?

In this case you should have a TIMESTAMP column. First you have to save that column. Just before updating data you should compare each value to ensure.

That's why this data type exists!

Shrum

Answer 5

In Sybase ASE, timestamp has different values for different databases in the same server.

use database_name

go

select @@dbts

thus it's obvious it is not related to Unix Epoch or any other time related reference.

It is different from timestamp from Sybase SQL Anywhere.

Nimwegen

See if you can determine the difference between a TIMESTAMP Datatype (the question), and @@DBTS, which the documentation informs me, is the database "time stamp", which is in fact a count of changes to the pages in the database. – Trihedron