从SQL数据库中获取以UTC时间存储的今天的记录

Question

我有用户创建的记录，我将创建日期以UTC存储在sql数据库中。

我想允许用户在UI上选择一个日期，该日期将提取当天的记录。

我的当地时间是UTC时间后的-4:00。所以我的问题是：

如果用户在当地时间上午9:00（UTC下午1:00）创建了一条记录，然后在当地时间晚上10:00（UTC第二天凌晨2:00）创建了另一条记录。当他们选择当天时，我如何查询这两条记录？

这些记录存储在UTC中，因此它们分为两天，但实际上它们在当地时间的同一天。

我该如何解决这个问题？我是否应该将记录存储在UTC中？

感谢

Answer 1

是-4小时的时差一直存在，还是您有来自不同时区的多个用户？若第一个适用，请考虑dateadd。例如：

SELECT dateadd(hour, -4, yourTimeStamp) as TimeStamp
, CAST(dateadd(hour, -4, yourTimeStamp) as DATE) as DateOfTimeStamp
FROM YourTable
WHERE CAST(dateadd(hour, -4, yourTimeStamp) as DATE) = '20160510'

编辑：您可以使用：

SELECT DATEDIFF(MINUTE, SYSDATETIME(), GETDATE()) AS MinutesOffsetUTCtoLocal

以获取客户端和机器之间的时间差（以分钟为单位）。将其集成如下：

SELECT dateadd(minute, DATEDIFF(MINUTE, SYSDATETIME(), GETDATE()), yourTimeStamp) as TimeStamp
, CAST(dateadd(minute, DATEDIFF(MINUTE, SYSDATETIME(), GETDATE()), yourTimeStamp) as DATE) as DateOfTimeStamp
FROM YourTable
WHERE CAST(dateadd(minute, DATEDIFF(MINUTE, SYSDATETIME(), GETDATE()), yourTimeStamp) as DATE) = '20160510'

Answer 2

我建议用以下列构建一个日历表（这对其他事情也很方便）-在我的示例中，它被称为：local_calendar

local_date      DATE     -- This marks the day (without time)
date_starts_utc DATETIME -- Local midnight converted to UTC
date_ends_utc   DATETIME -- Local midnight of the next day converted to UTC.

在合理的日期范围内填写此表。

从现在起，您可以将此表连接到任何UTC时间以获得本地日期，并将任何本地日期连接到UTC时间边界。

获取utc时间的本地日期

SELECT local_date
FROM local_calendar
WHERE @utcTime >= date_starts_utc AND @utcTime < date_ends_utc

获取本地日期的UTC边界

SELECT @lowerBoundary = date_starts_utc, @higherBoundary = date_ends_utc
FROM local_calendar
WHERE date = @localDate