是否有方法从timestamp类型列中获取DateTime值?

I need a select from table which does not have column that tells when row was inserted, only timestamp column (values like: 0x0000000000530278). Some data was imported to the table yesterday and now I need to find out what exactly was imported :(

我需要一个select from表，该表没有显示何时插入行的列，只有timestamp列(值如下:0x00000000000000530278)。昨天有一些数据被输入到表格中，现在我需要找出到底是什么输入的

Is there a way to do it using only timestamp info? Here I found that:

有没有一种方法可以只使用时间戳信息?我发现:

Timestamp is a 8 bytes sequential Hex number, that has nothing to do with neither the date nor the time.
时间戳是一个8字节顺序的十六进制数字，这与日期和时间无关。
To get the current value of timestamp, use: @@DBTS.
要获取时间戳的当前值，请使用:@ dbts。

Perhaps there is a way to find what was timestamp value around specific time? That would help to form a select. Or maybe there is a well known solution?

也许有一种方法可以找到特定时间的时间戳值是多少?这将有助于形成一个选择。或者也许有一个众所周知的解决方案?

8 个解决方案

#1

The Transact-SQL timestamp data type is a binary data type with no time-related values.

Transact-SQL时间戳数据类型是一种没有时间相关值的二进制数据类型。

So to answer your question: Is there a way to get DateTime value from timestamp type column?

因此，要回答您的问题:是否有方法从timestamp类型列中获取DateTime值?

The answer is: No

答案是:没有

#2

The timestamp datatype in SQL Server 2005 is a synonym of rowversion and is just a number that is automatically incremented with each row update.

SQL Server 2005中的时间戳数据类型是行版本的同义词，只是随着每一行更新而自动递增的数字。

You can cast it to bigint to see its value.

你可以把它丢给bigint看它的值。

To get what you want for new or updated rows, you should propably add another datetime column (lastupdate) and a trigger to update that column with each update.

要获取新行或更新行的所需内容，您应该可能添加另一个datetime列(lastupdate)和一个触发器，以便在每次更新时更新该列。

For rows that have already been inserted in the past I don't think that you can do something to find the exact time.

对于已经插入过去的行，我认为您不能做一些事情来找到确切的时间。

#3

I'm afraid it's not possible to convert/cast a TIMESTAMP to a DATETIME. They have entirely different uses and implementations that are incompatible.

恐怕不可能将时间戳转换为DATETIME。它们有完全不同的用途和不兼容的实现。

See this link http://www.sqlteam.com/article/timestamps-vs-datetime-data-types

看到这个链接http://www.sqlteam.com/article/timestamps-vs-datetime-data-types

Books on-line also says http://msdn.microsoft.com/en-us/library/aa260631.aspx

在线图书也有http://msdn.microsoft.com/en-us/library/aa260631.aspx

The SQL Server timestamp data type has nothing to do with times or dates. SQL Server timestamps are binary numbers that indicate the relative sequence in which data modifications took place in a database. The timestamp data type was originally implemented to support the SQL Server recovery algorithms.

SQL Server时间戳数据类型与时间或日期无关。SQL Server时间戳是二进制数，表示数据库中进行数据修改的相对序列。时间戳数据类型最初是为了支持SQL服务器恢复算法而实现的。

#4

Another answer to you question:

另一个问题的答案是:

If the timestamp column is the only resource for you recovery (no backups etc) you may try to use the following logic

如果时间戳列是惟一的恢复资源(没有备份等)，您可以尝试使用以下逻辑

Timestamp is simply a value of a counter that is incremented for each insert or update operation that is performed on a table that contains a timestamp column.

时间戳只是一个计数器的值，它对包含时间戳列的表执行的每个插入或更新操作进行递增。

If the data import that happened yesterday was one insert of several records you may see a sequence of numbers in the timestamp column like e.g:

如果昨天发生的数据导入是几个记录的一个插入，那么您可以在timestamp列中看到一个数字序列，例如:

0x00000000000007D1
0x00000000000007D2
0x00000000000007D3
0x00000000000007D4
0x00000000000007D5

The most recent sequence can be your added data (of course it is not guarantied) You con combine that knowledge with other things (like auto-increment column if you use them) to identify the records you are interested in.

最近的序列可以是您添加的数据(当然它不受保护)，您需要将这些知识与其他内容(如使用自动增量列)结合，以识别您感兴趣的记录。

#5

Other people correctly pointed out that the timestamp is a binary counter. Nevertheless, if in any table of your database, you have the timestamp and the datetime when it was recorded, you can use that piece of information to go from any timestamp to a date range. A log table is a good candidate for this purpose. Assuming your import table is "invoices", you could use a query like the following:

其他人正确地指出时间戳是一个二进制计数器。然而，如果在数据库的任何表中，您都有记录时的时间戳和日期时间，您可以使用该信息从任何时间戳到日期范围。日志表就是一个很好的例子。假设您的导入表是“发票”，您可以使用如下的查询:

WITH TS 
AS
(
SELECT 
    L1.LastDateUpdated, COALESCE(L2.LastDateUpdated, {TS '2099-12-31 00:00:00'}) as LastDateUpdatedTo,
    L1.[TIMESTAMP], L2.[TIMESTAMP] as [TIMESTAMPTo]
FROM 
(
    SELECT L1.[LastDateUpdated]
          ,L1.[TIMESTAMP]
          ,ROW_NUMBER() OVER (ORDER BY L1.[LastDateUpdated]) ID
    FROM [Log] L1
) L1
left join 
(
    SELECT L2.[LastDateUpdated]
          ,L2.[TIMESTAMP]
          ,ROW_NUMBER() OVER (ORDER BY L2.[LastDateUpdated]) ID
    FROM [Log] L2
) L2 
    ON L1.ID = L2.ID - 1
)
SELECT TS.LastDateUpdated, TS.LastDateUpdatedTo, * from [Invoices]
    inner join TS ON [Invoices].Timestamp between TS.Timestamp and 
TS.TIMESTAMPTo
ORDER BY TS.TIMESTAMPTo DESC

#6

I think your best bet is to restore a backup from before the inserts and compare the backuped table with the current table.

我认为最好的方法是从插入之前恢复备份，并将备份表与当前表进行比较。

#7

To identify new rows by timestamp you need to keep track of the timestamps that were there beforehand. In a pinch you could:

要通过时间戳识别新行，您需要跟踪预先存在的时间戳。必要时，你可以:

Restore a previous version somewhere else.
在其他地方恢复以前的版本。
Copy the data from both tables into a scratch database.
将两个表中的数据复制到一个临时数据库中。
Identify the inserted data from the timestamps present in one but not the other.
从一个时间戳中标识插入的数据，而不是另一个。

With a minor risk of false positives if anything else has been going on in the DB this will get you a reasonably good difference.

如果DB中发生了其他事情，那么就会有轻微的误报风险，这将给您带来相当好的差异。

For a more robust check you could calculate MD5 or SHA-1 hashes with Hashbytes on the row contents to give you a difference with a very low probability of collision (see this wikipedia article on Birthday attacks for a discussion of this problem).

为了进行更健壮的检查，您可以在行内容上计算带有Hashbytes的MD5或SHA-1散列，以使您在非常低的碰撞概率下获得差异(请参阅wikipedia关于生日攻击的文章，以了解有关这个问题的讨论)。

#8

I know it is too late but might help someone else.

我知道为时已晚，但可能会帮助别人。

Timestamp/RowVersion can be casted to BigInt but in any mean it cannot be compared to datetime.

可以将时间戳/行版本转换为BigInt，但无论如何不能与datetime进行比较。

Following statement is taken from MSDN

以下语句取自MSDN。

The Transact-SQL rowversion data type is not a date or time data type. timestamp is a deprecated synonym for rowversion.

Transact-SQL行版本数据类型不是日期或时间数据类型。时间戳是rowversion的不赞成的同义词。

For more detail refer here

更多细节请参考这里。

#1