如何从TIMESTAMP列获取日期/时间信息？

It seems that the TIMESTAMP information is encrypted in some way, where the date/time data is somehow encoded in binary. I just want to discover all the rows that were modified today.

似乎TIMESTAMP信息以某种方式加密，其中日期/时间数据以某种方式以二进制编码。我只是想发现今天修改过的所有行。

3 个解决方案

#1

TIMESTAMP is an unfortunate name the SQL Server team gave the data type. It is for concurrency, and has nothing to do with date or time - they've recommended using its alias, ROWVERSION to prevent confusion. From this Books Online article, "In DDL statements, use rowversion instead of timestamp wherever possible."

TIMESTAMP是SQL Server团队提供数据类型的不幸名称。这是为了并发，与日期或时间无关 - 他们建议使用别名ROWVERSION来防止混淆。从这本联机丛书文章“在DDL语句中，尽可能使用rowversion而不是时间戳”。

Unfortunately you won't be able to derive any date/time details from the ROWVERSION column you already have, but if this information is important, you should add CreatedDate / ModifiedDate columns, for example:

遗憾的是，您将无法从已有的ROWVERSION列中获取任何日期/时间详细信息，但如果此信息很重要，则应添加CreatedDate / ModifiedDate列，例如：

ALTER TABLE dbo.foo ADD CreatedDate DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP;
ALTER TABLE dbo.foo ADD ModifiedDate DATETIME NULL;

Then create a TRIGGER that fires on UPDATE to keep the ModifiedDate value current. You may need to decide whether you want the ModifiedDate to be NULL or equal to CreatedDate on initialization.

然后创建一个触发UPDATE的TRIGGER，以使ModifiedDate值保持最新。您可能需要确定在初始化时是否希望ModifiedDate为NULL或等于CreatedDate。

#2

TIMESTAMP is just an incremental, per-row value. It does not hold any actual date/time information.

TIMESTAMP只是一个增量的每行值。它不包含任何实际日期/时间信息。

What you need is for example an actual DATETIME column with its default value set to GETUTCDATE() or something like that.

你需要的是例如一个实际的DATETIME列，其默认值设置为GETUTCDATE（）或类似的东西。

#3

Depending on usage scenario and the scale of precision that you need you can use following technic: As far as TIMESTAMP is something like global counter you can add one global table with 2 columns:

根据使用场景和您需要的精度范围，您可以使用以下技术：就TIMESTAMP类似全局计数器而言，您可以添加一个包含2列的全局表：

datetime,timestamp

日期时间，时间戳

and make some JOB insert values there every N minutes (depending on required precision). Job will insert NOW() into datetime column and current TIMESTAMP value. In this way you get some kind of "time ruler" and you always can determine which timespan your particular TIMESTAMP from another table belongs to. Sample: You have timestamp value 0x000121 and look for timespan, when it was generated. Your table has values

并且每隔N分钟在那里制作一些JOB插入值（取决于所需的精度）。 Job将NOW（）插入datetime列和当前TIMESTAMP值。通过这种方式，您可以获得某种“时间标尺”，并且您始终可以确定来自另一个表的特定TIMESTAMP属于哪个时间跨度。示例：您有时间戳值0x000121并在生成时查找时间跨度。你的桌子有价值

20120501 12:00:00   0x000001
20120501 12:15:00   0x000061
20120501 12:30:00   0x000091
20120501 12:45:00   0x000151

Using select query you will be able to determine, that 0x000121 lies between 20120501 12:30:00 and 20120501 12:45:00

使用select查询，您将能够确定0x000121介于20120501 12:30:00和20120501 12:45:00之间

If you have no possibility to create such table/job you can look into database and determine other tables with timestamp and maybe you will be lucky and will find datetime column there as well (filled with NOW()), then you can use that table as "time ruler".

如果你没有可能创建这样的表/作业，你可以查看数据库并确定带有时间戳的其他表，也许你会很幸运，也会在那里找到datetime列（用NOW（）填充），然后你可以使用那个表作为“时间统治者”。

#1