MS SQL datetime精度问题 [英] MS SQL datetime precision problem

问题描述

我有一个这样的情况：两个人可能从两台不同的计算机上以相同的顺序（存储在MS SQL数据库中）工作。为了防止数据丢失的情况下，首先保存他的订单副本，然后稍后，第二个将保存他的副本并覆盖第一个，我已经添加了一个支票对最后一个保存的保存前的字段（datetime）。

I have a situation where two persons might work on the same order (stored in an MS SQL database) from two different computers. To prevent data loss in the case where one would save his copy of the order first, and then a little later the second would save his copy and overwrite the first, I've added a check against the lastSaved field (datetime) before saving.

代码看起来大致如下：

private bool orderIsChangedByOtherUser(Order localOrderCopy)
{
    // Look up fresh version of the order from the DB
    Order databaseOrder = orderService.GetByOrderId(localOrderCopy.Id);

    if (databaseOrder != null &&
        databaseOrder.LastSaved > localOrderCopy.LastSaved)
    {
        return true;
    }
    else
    {
        return false;
    }
}

这个工作大部分时间都可以，但是发现一个小错误。

This works for most of the time, but I have found one small bug.

如果 orderIsChangedByOtherUser 返回 false ，本地副本将具有 lastSaved 更新到当前时间，然后被持久化到数据库。本地副本和数据库中最后保存的 的值现在应该是一样的。但是，如果 orderIsChangedByOtherUser 再次运行，即使没有其他用户对数据库进行了更改，有时也会返回 true 。

If orderIsChangedByOtherUser returns false, the local copy will have its lastSaved updated to the current time and then be persisted to the database. The value of lastSaved in the local copy and the DB should now be the same. However, if orderIsChangedByOtherUser is run again, it sometimes returns true even though no other user has made changes to the DB.

当在Visual Studio中进行调试时，databaseOrder.LastSaved 和 localOrderCopy.LastSaved 看起来具有相同的值，但是当它们看起来更近时，它们有时间差几毫秒。

When debugging in Visual Studio, databaseOrder.LastSaved and localOrderCopy.LastSaved appear to have the same value, but when looking closer they some times differ by a few milliseconds.

我发现这篇文章有一个关于SQL中datetime的毫秒精度的简短通知：

I found this article with a short notice on the millisecond precision for datetime in SQL:

另一个问题是SQL Server
存储精度为
3.33毫秒（0. 00333秒）的DATETIME。

Another problem is that SQL Server stores DATETIME with a precision of 3.33 milliseconds (0. 00333 seconds).

我可以想到的解决方案这个问题是比较两个数据时间，如果它们差异小于10毫秒，则认为它们相等。

The solution I could think of for this problem, is to compare the two datetimes and consider them equal if they differ by less than say 10 milliseconds.

我的问题是：有没有更好/更安全的方式来比较MS SQL中的两个日期时间值，以确定它们是否完全相同？

My question to you is then: are there any better/safer ways to compare two datetime values in MS SQL to see if they are exactly the same?

推荐答案

我知道你说你不能改变类型，但如果这只是为了维持兼容性和您使用2008年可以将 lastSaved 字段更改为 DATETIME2 （与 DATETIME完全兼容），并使用 SYSDATETIME（）两者都有更高的精度。

I know you said you can't change the type, but if this is only to maintain compatibility & your using 2008 you could change the lastSaved field to DATETIME2 (which is fully compatible with DATETIME) and use SYSDATETIME() both of which have much greater precision.

这篇关于MS SQL datetime精度问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！