在select语句中将日期时间列从UTC转换为本地时间 [英] Convert Datetime column from UTC to local time in select statement

问题描述

我正在执行一些 SQL 选择查询，并希望将我的 UTC 日期时间列转换为本地时间，以便在查询结果中显示为本地时间.请注意，我不希望通过代码进行这种转换，而是希望在对我的数据库进行手动和随机 SQL 查询时进行.

I'm doing a few SQL select queries and would like to convert my UTC datetime column into local time to be displayed as local time in my query results. Note, I am NOT looking to do this conversion via code but rather when I am doing manual and random SQL queries against my databases.

推荐答案

您可以在 SQL Server 2008 或更高版本上执行以下操作:

You can do this as follows on SQL Server 2008 or greater:

SELECT CONVERT(datetime, 
               SWITCHOFFSET(CONVERT(datetimeoffset, 
                                    MyTable.UtcColumn), 
                            DATENAME(TzOffset, SYSDATETIMEOFFSET()))) 
       AS ColumnInLocalTime
FROM MyTable

你也可以做不那么冗长的:

You can also do the less verbose:

SELECT DATEADD(mi, DATEDIFF(mi, GETUTCDATE(), GETDATE()), MyTable.UtcColumn) 
       AS ColumnInLocalTime
FROM MyTable

无论您做什么，不要使用 - 来减去日期，因为该操作不是原子操作，并且您有时会由于两者之间的竞争条件而得到不确定的结果系统日期时间和本地日期时间在不同时间(即非原子地)检查.

Whatever you do, do not use - to subtract dates, because the operation is not atomic, and you will on occasion get indeterminate results due to race conditions between the system datetime and the local datetime being checked at different times (i.e., non-atomically).

请注意，此答案未考虑 DST.如果您想包括 DST 调整，另请参阅以下 SO 问题:

Please note that this answer does not take DST into account. If you want to include a DST adjustment, please also see the following SO question:

如何创建SQL Server 中的夏令时开始和结束函数

这篇关于在select语句中将日期时间列从UTC转换为本地时间的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！