如何从 TIMESTAMP 列中获取日期/时间信息? [英] How do I get date/time information from a TIMESTAMP column?
问题描述
似乎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
是 SQL Server 团队为数据类型提供的一个不幸的名称.它用于并发,与日期或时间无关 - 他们建议使用其别名 ROWVERSION
以防止混淆.来自 这篇在线图书文章,在 DDL 语句中,在任何地方使用 rowversion 而不是时间戳可能."
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."
遗憾的是,您将无法从已有的 ROWVERSION
列中推导出任何日期/时间详细信息,但如果此信息很重要,您应该添加 CreatedDate/ModifiedDate 列,例如:
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:
ALTER TABLE dbo.foo ADD CreatedDate DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP;
ALTER TABLE dbo.foo ADD ModifiedDate DATETIME NULL;
然后 创建一个在 上触发的
UPDATE 以保持 ModifiedDate 值是最新的.您可能需要决定是否希望 ModifiedDate 在初始化时为 TRIGGER
NULL
或等于 CreatedDate.
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.
这篇关于如何从 TIMESTAMP 列中获取日期/时间信息?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!