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:
- Record a date/time marking when the transaction starts
- Read/update data
- Check if another transaction modified the data
- 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.