获取今天午夜时间的UTC时间

Question

我有以下查询，它计算今天午夜的值（UTC）作为日期时间：

SELECT CONVERT(DATE,GETDATE())+(GETDATE()-GETUTCDATE())

结果: 2011年11月3日 19:00:00.000 (对于2011年11月4日的GMT-5时区)

不仅如此，有时它会返回这样的值:

2011-11-03 19:00:00.003
2011-11-03 19:00:00.007
2011-11-03 19:00:00.010

..., 这些是错误的！

一定有更好的方法来解决这个问题。

- Michael Goldshteyn

Answer 1

我已经提供了一个使用DATEADD和DATEDIFF与GETDATE()和GETUTCDATE()解决方案的答案，类似于原问题中给出的示例，但自那以后我发现datetimeoffset数据类型 在SQL Server 2008中添加。它存储带有时区偏移量的日期时间。

如何使用此类型取决于您是否要更改现有数据的数据类型。如果您不想更改任何内容，则以下语句将返回具有午夜本地时间的日期时间类型：

SELECT CONVERT(datetime, SWITCHOFFSET(CONVERT(datetimeoffset, 
    CONVERT(date, GETDATE())), 
    DATENAME(TzOffset, SYSDATETIMEOFFSET())))

您也可以使用以下方法将任何UTC时间转换为本地时间：

SELECT CONVERT(datetime, SWITCHOFFSET(CONVERT(datetimeoffset, 
        @myutctime, 
        DATENAME(TzOffset, SYSDATETIMEOFFSET())))

datetimeoffset类型仅适用于SQL2008及以上版本。如果您需要在2005及以下版本中执行此操作，可以使用类似于原始问题中的解决方案，但需要考虑到GETDATE() - GETUTCDATE()不是一个原子操作，并且在执行两者之间可能涉及毫秒差异。

SELECT DATEADD(minute, 
    DATEDIFF(minute, GETUTCDATE(), GETDATE()), 
    CONVERT(datetime, CONVERT(date, GETDATE())))

这将获取 GETDATE() 和 GETUTCDATE() 之间的分钟数，并将它们添加到当地午夜时间。不幸的是，由于 DATEADD 在给定日期时无法处理分钟，因此您必须将其转换回日期时间。我建议将其封装为用户定义函数，以使其看起来更简洁。例如：

CREATE FUNCTION dbo.MidnightASUTC(@dt as datetime)
RETURNS datetime
AS
BEGIN
    RETURN DATEADD(minute, 
        DATEDIFF(minute, GETUTCDATE(), GETDATE()),
        CONVERT(datetime, CONVERT(date, @dt)))
END

SELECT dbo.MidnightAsUTC(GETDATE())

- Richard

---

你需要在DATEDIFF函数的调用中反转GETDATE()和GETUTCDATE()，即DATEDIFF(hour, GETUTCDATE(), GETDATE())，以获得正确的结果。但是我尝试运行了大约100次，至少得到了正确的时间。 - Michael Goldshteyn

糟糕，我搞反了。我还改变了答案，使用DATEADD(minute...)，因为小时方法无法处理与UTC相差半小时的国家。 - Richard

我想知道你提供的UDF是否会有性能损失，是否有意义将其作为内联TVF，返回一个1行/1列结果与值？ - Michael Goldshteyn

我已经完全修订了这个答案，因为我刚刚发现datetimeoffset数据类型，它可以存储时区偏移量。原始答案保留供需要在SQL2008之前执行此操作的人使用。 - Richard

Answer 2

针对您所描述的特定情况（“UTC今天午夜值作为日期时间”），编程方法是有意义的，但如果您需要将其扩展到不同的问题（例如，“这个夏天UTC午夜是什么时候？”），您可能需要使用日历表（以考虑夏令时等因素）。

- Stuart Ainsworth