时间戳

首页 > 解决方案 > 使用 DateTimeOffset 作为参数问题进行过滤

c# - 使用 DateTimeOffset 作为参数问题进行过滤

问题描述

我正在使用 C# 程序玩日期。

我想过滤具有 DateTime、DateTime2、DateTimeOffset 列的任何表。

我将LastRefreshDateas存储DateTimeOffSet在 UTC 中，并使用它来过滤这些表上的数据。LastRefreshDate我根据用于在这些表中存储日期的时区调整偏移量（使用 NodaTime） 。通常，它是由用户提供的。

所以我创建了一个测试样本来解释这个问题。通常，SQL 查询是动态的，参数也是如此。这是示例代码：

[TestMethod]
public void Test()
{
    using (SqlConnection connection = new SqlConnection("Server=myserver;Database=mydb;User ID=admin;Password=admin"))
    {
        connection.Open();
        using (SqlCommand command = new SqlCommand("SELECT [TimeStamp] FROM  [dbo].[DATA] WHERE [TimeStamp] >= @p0", connection))
        {
            string datestring = "2019-06-18 13:35:20.1133868 -04:00";

            // Does not work
            // DateTimeOffset p0 = DateTimeOffset.Parse(datestring, CultureInfo.InvariantCulture);
            // Does work
            DateTime p0 = DateTime.Parse(datestring, CultureInfo.InvariantCulture);
            command.Parameters.AddWithValue("@p0", p0);
            using (SqlDataReader reader = command.ExecuteReader())
            {
                var dataTable = new DataTable();
                dataTable.Load(reader);
                var result = dataTable.Rows.Count == 0;
            }
        }
    }
}

我创建了 2 个 SQL 小提琴来演示这个问题。顺便说一句，我运行了 SQL Server Profiler，生成的查询类似于小提琴中的查询。

日期时间小提琴：http ://sqlfiddle.com/#!18/a06be/1

declare @p0 datetime = '2019-06-18 13:35:20'
SELECT 
    [TimeStamp]
FROM 
    [dbo].[DATA]
WHERE 
    ([TimeStamp] >= @p0)

DateTimeOffSet 小提琴：http ://sqlfiddle.com/#!18/a06be/2

declare @p0 datetimeoffset(7) ='2019-06-18 13:35:20.1133868 -04:00'
SELECT [TimeStamp]
FROM 
    [dbo].[DATA] 
WHERE 
    ([TimeStamp] >= @p0 )

我做了更多的测试。通过直接应用强制转换，SQL 查询可以工作。似乎 SQL Server隐式转换的行为方式与显式转换不同。这是测试用例：

declare @p0 datetime
set @p0 = '2019-06-18 17:48:00.00'
declare @p1 datetimeoffset(7)
set @p1 = '2019-06-18 17:47:00.5385563 -04:00'

select 1 
where @p0 > cast(@p1 as datetime) -- working
--where @p0 > @p1                       -- not working

标签： c#sql-servertimezonetimezone-offsetnodatime

解决方案

A few things:

  • In SQL Server, if you use CAST, or CONVERT without specifying a style, the default style is 0, which when converting a datetimeoffset to either a datetime or datetime2 simply takes the date and time value from the datetimeoffset without considering the offset. If you want to take the offset into account, then use CONVERT and pass 1 for the style:

    DECLARE @p0 datetimeoffset = '2019-06-18 13:35:20.1133868 -04:00'
SELECT convert(datetime, @p0, 0) as 'A', convert(datetime, @p0, 1) as 'B'
-- A = 2019-06-18T13:35:20.113Z
-- B = 2019-06-18T17:35:20.113Z

  • When querying a datetime or datetime2 field using a datetimeoffset parameter, the offset is indeed taken into account in the implicit conversion (it is like B above).

  • On the C# side, be careful about DateTime.Parse. By default, it emits a local time based value when an offset is provided. If you check, you'll see p0.Kind == DateTimeKind.Local. You could pass DateTimeStyles.AdjustToUniversal, but a better idea is to parse as a DateTimeOffset like you showed in your "doesn't work" code. But then instead of passing the full DateTimeOffset, pass the UtcDateTime property:

    DateTime p0 = DateTimeOffset.Parse(datestring, CultureInfo.InvariantCulture).UtcDateTime;

  • For both performance and stability reasons, you might consider using ParseExact or TryParseExact instead of Parse. Or, since you said you already are using Noda Time, you can use its text parsing features with an OffsetDateTimePattern. From there you'd either call .ToDateTimeOffset().UtcDateTime, or .ToInstant().ToDateTimeUtc().

  • Alternatively, you could just define your SQL database columns as datetimeoffset, then you can pass any DateTimeOffset parameter and it will be normalized to UTC when querying.

推荐阅读