SQL Sybase 中神秘的“时间戳”数据类型是什么？

What is the mysterious 'timestamp' datatype in Sybase?

提问by Eric Johnson

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

我最近在工作中发现了我们 Sybase 数据库中的一个表，它使用了一个“时间戳”类型的列。如果我使用这种神秘的时间戳数据类型创建一个表

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':

我从'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':

0x000100000e1ce4ea 对我来说看起来不太像时间戳。另外，我从“sp_help 时间戳”看到了这个输出：

 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?

1. 时间戳是什么鬼？
2. 它与时间或日期有任何关系吗？
3. 我可以将其转换为日期时间吗？
4. 如果它不是时间或日期，你用它做什么？

采纳答案by Eric Johnson

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. TIMESTAMPand CURRENT TIMESTAMP. The difference is that CURRENT TIMESTAMPis only set on insert.

每次插入或更新带有时间戳列的行时，时间戳列都会自动更新。请注意，实际上有两种时间戳。 TIMESTAMP和CURRENT TIMESTAMP。不同之处在于CURRENT TIMESTAMP仅在插入时设置。

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

Sybase文档停在那里，让我想知道为什么 f*rainbow!*k 任何人都会使用数据类型时间戳。令人高兴的是，我发现了一些其他的讨论，并推断出它在实现乐观并发控制时的用途。

Concurrency controlis a method of ensuring that multiple transactions can run at/around the same time and still result in correct data. Optimistic concurrency controlis 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

1. 记录交易开始时的日期/时间标记
2. 读取/更新数据
3. 检查另一个事务是否修改了数据
4. 提交或回滚

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.

可以在此算法的步骤 1 和 3 中使用 Sybase 的时间戳数据类型，而不是使用日期/时间。但在我看来，它并不像使用日期时间数据类型那样为您节省很多工作。我想它可能会表现得更好。

回答by ArBR

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

最近有人问我是否可以将 TIMESTAMP SYBASE IQ数据类型转换为 DATE；我一直避免使用这种数据类型，因为它很黑暗。经过几个小时的阅读SYBASE 文档并进行了一些测试，以下是我的结论：

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

• 是 12 位数字，存储为 BINARY（这可能因环境而异）
• 它表示自 1970 年 1 月 1 日以来的以微秒为单位的值
• Sybase 不包括用于转换它们的直接函数
• 每次插入记录时都会自动建立

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

这是将 TIMESTAMP 转换为 DATE 的 SQL 语句：

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:

可以通过使用在线 EPOCH 转换器来证明转换，如下所示：

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

• 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.

注意：对于SYBASE ASE，TIMESTAMP 类型不是有效的UNIX-EPOCH。

回答by Tim B

(This is an answer posted as a separate question Answers to the mysterious Sybase ASE 'timestamp' datatype questionsby 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)

（这是一个作为单独问题的答案发布的神秘 Sybase ASE 'timestamp' 数据类型问题的答案，用户没有代表将其添加到此处。我已将其复制为 Community Wiki，因为我不想声明信用为它，但它应该在这里）

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

对 Q#1 的回答：“时间戳到底是什么？”

? 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.

? Sybase ASE 数据库的时间戳集中保存在该数据库的内部内存表“dbtable”中——该表是在数据库联机时构建的。您可以通过 select @@dbts 查询当前的 DB 时间戳 - 请注意，这个 varbinary(8) 'Database' 时间戳值是平台相关的，即受到大字节序和小字节序的影响。

? 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).

? 每个用户表可能有一个时间戳列，用于保存给定行的 INSERT / UPDATE 的“数据库”时间戳值。在成功完成 TSQL DML 命令后，所有“表”时间戳列值都由 ASE（就像标识列）自动维护。但是，与“数据库”时间戳不同，“表”时间戳值与平台无关，因为无论 O/S 平台的字节序如何，它们始终以大端字节序保存（有关详细信息，请参阅下面的更多信息）。

---

---

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

对 Q#2 的回答：“它与时间或日期有任何关系吗？”

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?'

对 Q#3 的回答：“我可以将其转换为日期时间吗？”

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?'

对 Q#4 的回答：“如果它不是时间或日期，你用它做什么？”

? 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.

? 每当修改或创建数据库中的页面时，“数据库”时间戳都会增加 1，而受影响页面的“本地”时间戳（在其页眉内）然后与该时间点的“数据库”时间戳同步。

? 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.

? 与当前的“数据库”时间戳相比，数据库页面的“本地”时间戳反映了该页面上次更新或首次创建的相对年龄；因此，ASE 可以告诉数据库中所有页面的更新/创建的时间顺序。

? 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

(1) 'Database' 和 'Local' 时间戳存储在 3 部分中，并且依赖于操作系统平台字节序。例如 0xHHHH 0000 LLLLLLLL

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

• 2 字节高位 - 0xHHHH
• 2 字节填充符 - 0x0000
• 4 字节低位 - 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) 用户 'Table' 时间戳也存储在 3 部分中，但它始终处于 Big-endian 方向。例如 0x0000 HHHH LLLLLLLL

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

• 2 字节填充符 - 0x0000
• 2 字节高位 - 0xHHHH
• 4 字节低位 - 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).

(3) 数据库时间戳保存在给定数据库（当数据库上线时创建）的内存系统表 dbtable 中。

• 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).

• 注 1 -“表”时间戳列值与数据库表的数据和/或索引页中的其他列值一样保存，其中定义了时间戳列。
• 注意 2 - 请注意，通过 SELECT @@dbts 查询当前数据库的“数据库”时间戳会返回其十六进制表示，该表示受操作系统平台的字节顺序影响。
• Note3 - 相反，通过 DBCC dbtable（不推荐）查询“数据库”时间戳会返回其 Big-endian 十六进制表示，因此，它是平台无关的。
• 警告 - 当给定数据库的“数据库”时间戳接近其最大限制（0xFFFF，0xFFFFFFFF）时，根据数据库中插入/更新操作的频率，可能需要十年或更长时间才能达到这一点，ASE 将抛出警告并且无法进一步插入/更新 - 唯一的选择是使用 BCP（加上通过 sp_showtext 的存储过程）从所有对象导出数据，删除数据库，再次创建它（使用新的 Near-zer 'Database'时间戳）并导入数据（和存储过程）。

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

仅供参考 - 上述答案、提示和技巧是真实而准确的，因为我在 Sybase 工作，现在在拥有产品 ASE 的 SAP 工作。

回答by Bur?in

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 TIMESTAMPcolumn. First you have to save that column. Just before updating data you should compare each value to ensure.

在这种情况下，您应该有一个TIMESTAMP列。首先，您必须保存该列。在更新数据之前，您应该比较每个值以确保。

That's why this data type exists!

这就是这种数据类型存在的原因！

回答by user2868127

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

在 Sybase ASE 中，对于同一服务器中的不同数据库，时间戳具有不同的值。

use database_name

使用数据库名称

go

走

select @@dbts

选择@@dbts

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

因此很明显它与 Unix Epoch 或任何其他时间相关的参考无关。

It is different from timestamp from Sybase SQL Anywhere.

它不同于来自 Sybase SQL Anywhere 的时间戳。