DbFunctions.TruncateTime 在不同服务器时区的行为是否不同? [英] Does DbFunctions.TruncateTime behave differently in different server time zones?

问题描述

我不确定我的问题标题是否完美 - 所以请允许我进一步解释.

I'm not sure that my question Title is perfect - so please allow me to explain a little further.

以下是一些测试数据的快照:

这是我的代码:

Function TestDb() As ActionResult
   Dim clientLocId As Integer = 23
   Dim showDate As New Date
   showDate = New Date(2015, 8, 14)
   'showDate = New Date(2015, 9, 22)
   'showDate = New Date(2015, 9, 27)

   Dim orderRecs = db.Orders.Where(Function(x) x.ClientLocationId = clientLocId AndAlso x.OrderNumber IsNot Nothing _
                    AndAlso x.DateCompletedUtc IsNot Nothing _
                    AndAlso DbFunctions.TruncateTime(x.OrderDateLoc) = showDate.Date) _
                    .OrderByDescending(Function(x) x.OrderDateUtc)

   Stop
End Function

这就是我的问题:

订单日期 09/27/2015 和 09/22/2015 的行使用上述逻辑正确查询 - 为每个请求的日期生成 1 行.但是 - 对 08/14/2015 日期的查询不会产生任何结果.如果这很重要，我现在在 -04:00 时区.如果我将时区 [edit] in the row data [end edit] 更改为 -04:00 2 08/14/2015 行查询正确.

The rows for Order Dates 09/27/2015 and 09/22/2015 query properly with the logic above - yielding 1 row for each date requested. BUT - a query for the 08/14/2015 date yields NOTHING. I am in the -04:00 timezone now, if that matters. If I change the timezone [edit] in the row data [end edit] to -04:00 the 2 08/14/2015 rows query properly.

我用谷歌搜索试图找到这个问题的答案，但已经干了.有人可以权衡我的问题吗?

I have googled to try to find answers to this but have come up dry. Can someone weigh in on my problem?

[更新]:解决方法这是基于@PiotrAuguscik 此线程的建议的解决方法(目前)，建议先将查询转换为列表:

[UPDATE]: Workaround Here's a workaround (for now) based on a suggestion from this thread by @PiotrAuguscik suggesting to convert the query first to a list:

Dim orderRecs = (db.Orders.Where(Function(x) x.ClientLocationId = clientLocId AndAlso x.OrderNumber IsNot Nothing _
                AndAlso x.DateCompletedUtc IsNot Nothing).ToList) _
                .Where(Function(x) x.OrderDateLoc.Value.Date = showDate.Date) _
                .OrderByDescending(Function(x) x.OrderDateUtc)

它有点硬皮"，但它有效.但是，我当然想知道为什么时区与 DbFunctions.TruncateTime() 有任何关系.

It's a little "crusty", but it works. I sure would like to know, however, why timezones would have anything to do with DbFunctions.TruncateTime().

[更新 #2] 正确的解决方案代码来自马特约翰逊的回答

Dim orderRecs = db.Orders.Where(Function(x) x.ClientLocationId = clientLocId AndAlso x.OrderNumber IsNot Nothing _
                          AndAlso x.DateCompletedUtc IsNot Nothing AndAlso
                          (x.OrderDateLoc >= showDateDto AndAlso x.OrderDateLoc < showDateDto.AddDays(1))) _
                          .OrderByDescending(Function(x) x.OrderDateUtc)

<小时>

推荐答案

一些事情:

• 您的原始查询和解决方法都是non-sargable.您永远不应该在 WHERE 子句中操作比较的左侧.如果这样做，数据库将无法使用任何索引，并且您拥有的数据越多，速度就会越来越慢.相反，执行范围查询.

• Both your original query and your workaround are non-sargable. You should never manipulate the left side of an comparison in a WHERE clause. If you do, the database can't use any indexes and will get slower and slower the more data you have. Instead, do a range query.

看起来您的表中有 datetimeoffset 类型.这些代表特定的时刻，因此与两个 datetimeoffset 值的比较是基于它们的 UTC 等效值 - 而不是它们的本地显示时间.值也以这种方式编入索引.

It would appear you have datetimeoffset types in your table. These represent specific moments in time, thus comparisons against two datetimeoffset values are done based on their UTC equivalents - not on their local display time. Values are indexed this way as well.

并非每个人都在同一时间遵守相同的日历日期.你需要问问自己，我要找谁约会?"

Not everyone observes the same calendar date at the same time. You'll need to ask yourself, "who's date am I asking for?"

• 如果是进行查询的人的日期，那么您的输入值应该反映这一点.不是根据本地时间将 VB Date(它是一个 System.DateTime)传递到您的查询中，而是传入基于 UTC 的 DateTime 或 DateTimeOffset.请记住，您需要进行范围查询，因此您将计算它们的对，作为半开区间.换句话说:

• If it's the date of the person making the query, then your input values should reflect that. Instead of passing into your query a VB Date (which is a System.DateTime) in terms of local time, either pass in a UTC-based DateTime, or a DateTimeOffset. Remember, you'll need to do a range query, so you would calculate a pair of them, as a half-open interval. In other words:

// this example uses the local time zone, but there are other ways also.
DateTimeOffset startDto = new DateTimeOffset(showDate.Date)
DateTimeOffset endDto = new DateTimeOffset(showDate.Date.AddDays(1))

// then in the query...
...   x.OrderDateLoc >= startDto && x.OrderDateLoc < endDto

如果您希望匹配记录的本地日期，那么您在 SQL Server 数据库中还有其他工作要做.

If you're looking to match the local date as it is recorded, then you have additional work to do in your SQL Server database.

• 首先，您需要通过 convert(datetime2, yourDateTimeOffset) 去除偏移量，或者仅通过 convert(date, yourDateTimeOffset) 计算原始本地日期代码>.您应该在 计算列 中执行此操作，以便您还可以创建索引

• First you'll need to strip away the offset by convert(datetime2, yourDateTimeOffset), or just compute the raw local date by convert(date, yourDateTimeOffset). You should do this in a computed column so you can also create an index on it.

然后，您可以使用该计算列进行范围查询，或者如果您计算到日期，那么您可以对其进行相等比较.

Then later, you can use that computed column to do the range query, or if you computed down to the date then you can just do an equality comparison against that.

一般来说，我会避免在 where 子句中使用 DbFunctions.TruncateTime.当用于 datetimeoffset 字段时，它会被转换为一些相当低效的 SQL，如下所示:

In general, I'd avoid using DbFunctions.TruncateTime in a where clause. It gets converted to some fairly inefficient SQL that looks like this when used against a datetimeoffset field:

convert(datetimeoffset, convert(varchar(255), yourField, 102) + ' 00:00:00 ' + Right(convert(varchar(255), yourField, 121), 6), 102)

本质上，这使用字符串重新构建 datetimeoffset，同时保留偏移量但将时间设置为午夜，这可能不是您真正想要做的.您可以在 SQL Profiler 中亲自查看.

Essentially, this uses strings to re-build the datetimeoffset while retaining the offset but setting the time to midnight, which is probably not what you really want to do. You can see this yourself in SQL Profiler.

这篇关于DbFunctions.TruncateTime 在不同服务器时区的行为是否不同?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！