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

发布于 2024-12-13 20:02:50 字数 359 浏览 0 评论 0原文

我有以下查询，它将今天的午夜值 (UTC) 计算为日期时间：

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

结果：2011-11-03 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

...，这是错误的！

必须有更好的方法来做到这一点。

原文

I have the following query which calculates today's midnight value (UTC) as a datetime:

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

Result: 2011-11-03 19:00:00.000 (for GMT-5 on Nov. 4, 2011)

Not only that, but on occasion, it returns values like these:

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

..., which are wrong!

There must be a better way to do this.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

ゝ杯具 2024-12-20 20:02:50

我已经通过使用 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() - GEUTCDATE() 不是原子操作，并且可能会涉及两者执行时之间的毫秒差异。

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())

I already answered this with a solution using DATEADD and DATEDIFF with GETDATE() and GETUTCDATE(), similar to the example given in the original question, but since then I've discovered the datetimeoffset data type added in SQL Server 2008. This stores a datetime along with a timezone offset.

How you use this type will depend on whether you want to change the data type of your existing data. If you don't want to change anything, the following statement will return a datetime type with the local time of midnight:

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

You could also convert any UTC time into local time using:

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

The datetimeoffset type is only available using SQL2008 and above. If you need to do this with 2005 and below, you can use a solution similar to the one in the original question, but altered to account for the fact that GETDATE() - GETUTCDATE() is not an atomic operation and will likely involve milliseconds of difference between when the two are executed.

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

This will take the number minutes between GETDATE() and GETUTCDATE() and add them onto the local midnight time. Unfortunately, you have to convert back from date to datetime as DATEADD won't work with minutes if you give it a date. I'd suggest wrapping this into a user-defined function to make it look less verbose, e.g.

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())

回复收藏 0 原文