SQL 有没有办法从时间戳类型列中获取 DateTime 值？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/177970/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Is there a way to get DateTime value from timestamp type column?
提问by Dandikas
I need a select from table which does not have column that tells when row was inserted, only timestampcolumn (values like: 0x0000000000530278). Some data was imported to the table yesterday and now I need to find out what exactly was imported :(
我需要从表中进行选择，该表没有指示何时插入行的列，只有timestamp列（值如：0x0000000000530278）。昨天将一些数据导入表中，现在我需要找出究竟导入了什么:(
Is there a way to do it using only timestampinfo? HereI found that:
有没有办法只使用timestamp信息来做到这一点？在这里我发现：
- Timestamp is a 8 bytes sequential Hex number, that has nothing to do with neither the date nor the time.
- To get the current value of timestamp, use: @@DBTS.
- 时间戳是一个 8 字节的连续十六进制数，与日期和时间无关。
- 要获取时间戳的当前值，请使用：@@DBTS。
Perhaps there is a way to find what was timestampvalue around specific time? That would help to form a select. Or maybe there is a well known solution?
也许有一种方法可以找到timestamp特定时间周围的价值？这将有助于形成一个选择。或者也许有一个众所周知的解决方案？
回答by kristof
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?
所以要回答你的问题：有没有办法从时间戳类型列中获取 DateTime 值？
The answer is: No
答案是：没有
回答by Panagiotis Korros
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 中的时间戳数据类型是 rowversion 的同义词，它只是一个随行更新而自动递增的数字。
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.
为了获得您想要的新行或更新行，您应该适当地添加另一个日期时间列 (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.
对于过去已经插入的行，我认为您无法找到确切时间。
回答by evilhomer
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.
恐怕无法将 TIMESTAMP 转换/转换为 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 Server 恢复算法而实现的。
回答by kristof
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:
如果昨天发生的数据导入是多条记录的一次插入，您可能会在时间戳列中看到一系列数字，例如：
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.
最近的序列可以是您添加的数据（当然它没有保证）您可以将该知识与其他内容（如使用自动增量列）相结合，以识别您感兴趣的记录。
回答by ildanny
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
回答by Jonas Lincoln
I think your best bet is to restore a backup from before the inserts and compare the backuped table with the current table.
我认为最好的办法是从插入之前恢复备份并将备份表与当前表进行比较。
回答by ConcernedOfTunbridgeWells
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.
如果数据库中发生任何其他事情，误报的风险很小，这将为您带来相当不错的差异。
For a more robust check you could calculate MD5 or SHA-1 hashes with Hashbyteson the row contents to give you a difference with a very low probability of collision (see this wikipedia article on Birthday attacksfor a discussion of this problem).
要进行更可靠的检查，您可以在行内容上使用Hashbytes计算 MD5 或 SHA-1 哈希值，以提供极低碰撞概率的差异（有关此问题的讨论，请参阅此维基百科文章关于生日攻击）。
回答by Ashish Singh
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.
Timestamp/RowVersion 可以转换为 BigInt，但无论如何它都不能与 datetime 进行比较。
Following statement is taken from MSDN
以下声明摘自MSDN
The Transact-SQL rowversiondata type is not a date or time data type. timestampis a deprecated synonym for rowversion.
Transact-SQL rowversion数据类型不是日期或时间数据类型。timestamp是rowversion的弃用同义词。
For more detail refer here
有关更多详细信息，请参阅此处
