1ms引发的问题

2023-07-31 17:07:04

最近在跟SQLServer数据库进行交互的时候发现一个奇怪的问题，在往数据库里边插入日期型数据的时候，在C#里面赋值的为 2014/05/19 23:59:59，但是存到数据库里边就变成了2014/05/20 00:00:00。

问题场景

当时需求是这样的，产品的销售策略要求管理员输入一个产品销售的开始日期SalesStart和结束日期SalesEnd，然后业务会根据当前的时间判断是否在这个产品销售范围内，如果不在则显示未开始或者已过期，所以存储的时候，对SalesEnd进行了处理，在存到数据库的时候，保存的是当天的23:59:59，当时我的处理是这样的：在截止日期加1天然后减去以1毫秒，代码如下：

ProductSalesPolicyModel productSaleModel;
productSaleModel = new ProductSalesPolicyModel();

productSaleModel.SalesStart = item.SaleStartTime;
productSaleModel.ValidStart = item.ValidStartTime;
productSaleModel.SalesEnd = item.SaleEndTime.AddDays(1).AddMilliseconds(-1);
productSaleModel.ValidEnd = item.ValidEndTime.AddDays(1).AddMilliseconds(-1);

然后利用企业库往数据库中使用某个存储过程插入数据，参数赋值的代码如下：

DataParameterCollection parameters;
parameters = new DataParameterCollection();
parameters.Add("SalesStart", DbType.DateTime, salePolicyModel.SalesStart);
parameters.Add("ValidStart", DbType.DateTime, salePolicyModel.ValidStart);
parameters.Add("ValidEnd", DbType.DateTime, salePolicyModel.ValidEnd);
parameters.Add("SalesEnd", DbType.DateTime, salePolicyModel.SalesEnd);

执行插入操作的细节就不写了。

执行完成之后，结果如下：

在数据库中ProductSalePolicy的表结构如下，其中ValidEnd的类型也是datetime

原因分析

初步分析，有可能是C#中DateTime和SQL Server中datetime数据精度不一致导致的

SQL Server中的时间精度

1. SQL Server中datetime精度

于是到MSDN上去找Transact-SQL中的datetime类型，果不其然，SQL中datetime字段的取值范围为：

日期范围为：1753.1.1 ~ 9999.12.31
时间范围为：00:00:00 ~23:59:59.997

可见，datetime类型的时间精度是3.33毫秒，如果超过该精度会进行近似到0.000，0.003，0.007 秒，比如：

输入值	SQL 存储值
01/01/98 23:59:59.999	1998-01-02 00:00:00.000
01/01/98 23:59:59.995 01/01/98 23:59:59.996 01/01/98 23:59:59.997 01/01/98 23:59:59.998	1998-01-01 23:59:59.997
01/01/98 23:59:59.992 01/01/98 23:59:59.993 01/01/98 23:59:59.994	1998-01-01 23:59:59.993
01/01/98 23:59:59.990 01/01/98 23:59:59.991	1998-01-01 23:59:59.990

2. SQL Server中datetime2精度

鉴于SQL Server的datetime精度只有3.33毫秒，从SQL Server2008开始，提供了datetime2类型，该类型的时间精度为00:00:00 ~ 23:59:59.9999999 即100纳秒，10^-4毫秒。

在System.Data这个主要和外部数据进行交互的名字空间中的DbType枚举可以看到，它提供了DataTime，和DataTime2这两种类型，分别对应了SQL Server中的这两种时间精度，从注释中可以看出，DataTime2的精度是100纳秒

namespace System.Data
{
    // Summary:
    //     Specifies the data type of a field, a property, or a Parameter object of
    //     a .NET Framework data provider.
    public enum DbType
    {
        //
        // Summary:
        //     A type representing a date and time value.
        DateTime = 6,
        //
        // Summary:
        //     Date and time data. Date value range is from January 1,1 AD through December
        //     31, 9999 AD. Time value range is 00:00:00 through 23:59:59.9999999 with an
        //     accuracy of 100 nanoseconds.
        DateTime2 = 26,
    }
}

C#中Datetime精度

现在再看看C#中的DataTime精度，因为我们在给存储过程中的ValidEnd参数进行赋值的时候定义的类型为DbType.Datetime ，同样，在MSDN上，有对其精度的说明：

Time values are measured in 100-nanosecond units called ticks

100ns就是DateTime的精度，即10^-4毫秒。10^-4毫秒也是为DateTime类型的精度

通常我们看到的C#中的DateTime类型精确到100纳秒，即最大时间为23:59:59.9999999。这后面的也就是我们常用到的tick的精度。

原因

因为我在C#代码中处理的时候是item.SaleEndTime.AddDays(1).AddMilliseconds(-1);所以时间精度为0.0001毫秒，为2014/12/16 23:59:59.9990000，然而数据库中字段使用的是datetime类型，该类型的精度为3.33毫秒，所以存到SQL Server的时候被round到了2014/12/17 00:00:000000，从前面的分析也可以看出这个问题。

解决方法

根据分析，有两种方式:

一种是将数据库中的该类型从datetime改为datetime2类型，但是这个类型只有SQL Server 2008及以上版本才支持，他的精度和C# 里面的DateTime数据类型的精度是一致的。
再一种就是修改我们传到SQL Server中的值了，在一些不需要那么高的精度的场景下，比如本文中对结束日期添加23:59:59的这一处理，当时想都没想直接是AddMilliseconds，减的是1ms，而数据库中的精度是3.3ms，所以导致被SQL Server Round到了第二天的零点。这里把 AddMilliseconds改成AddSecond(-1)减1s即可。

总结

C# 中的DateTime的精度和SQL Server中的datetime2的精度(SQL Server 2008及以上版本才有) 是一致的，均为100纳秒(10^-4毫秒)，而SQL Server中的datetime类型精度只有3.33毫秒，所以如果把C# 中的DateTime类型存储到SQL Server中的datetime中时，会出现精度丢失的情况，在对精度要求较高的系统中，需要注意。

码农公寓

问题场景

原因分析

SQL Server中的时间精度

C#中Datetime精度

原因

解决方法

总结

相关文章