What is the mysterious ‘timestamp’ datatype in Sybase?

by

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.

More Related Contents:

  1. Cannot insert explicit value for identity column in table ‘table’ when IDENTITY_INSERT is set to OFF
  2. How to get list of values in GROUP_BY clause?
  3. Which one of these Queries are wrong and which is correct (SQL) [closed]
  4. ORA-01843 Not a valid month
  5. Explicit vs implicit SQL joins
  6. Row Offset in SQL Server
  7. SQL Server recursive self join
  8. Left Outer Join Not Working?
  9. How to simulate UNPIVOT in Access?
  10. Real life example, when to use OUTER / CROSS APPLY in SQL
  11. How to get script of SQL Server data? [duplicate]
  12. Sql Server equivalent of a COUNTIF aggregate function
  13. Using ISNULL vs using COALESCE for checking a specific condition?
  14. PostgreSQL: Which Datatype should be used for Currency?
  15. Add unique constraint to combination of two columns
  16. What does a transaction around a single statement do?
  17. format interval with to_char
  18. MySQL: GROUP_CONCAT with LEFT JOIN
  19. Joining multiple tables in SQL
  20. Array in IN() clause oracle PLSQL
  21. Achieving ROW_NUMBER / PARTITION BY in MS Access
  22. Get the character between first 2 special character in SQL
  23. How to use Timestamp_to_scn and Scn_to_timestamp in Oracle?
  24. How to get a list column names and datatypes of a table in PostgreSQL?
  25. Inner Joining three tables
  26. What is the most appropriate data type for storing an IP address in SQL server? [duplicate]
  27. How do MySQL views work?
  28. Using Raw SQL with Doctrine
  29. How to bulk insert only new rows in PostreSQL
  30. Update a table with data from another table

Leave a Comment