Eric Johnson ask the following questions in Aug 2010. "What is the mysterious 'timestamp' datatype in Sybase?
- What the heck is a timestamp?
- Does it have any relation at all to time or date?
- Can I convert it to a datetime?
- If its not a time or a date, what do you use it for?"
I attempted to answer the above post but this site would not accept a new answer due to the age of the original post. As the subject matter remains a FAQ amongst SAP/Sybase ASE professional, I have decided to re-post the original questions above and give hopefully some clear answers below.
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.