在 SQL 2005 中有效地在 UTC 和本地(即 PST)时间之间转换日期 [英] Effectively Converting dates between UTC and Local (ie. PST) time in SQL 2005

问题描述

将 UTC 日期时间转换为本地日期时间的最佳方法是什么.它不像 getutcdate() 和 getdate() 的差异那么简单，因为差异会根据日期而变化.

What is the best way to convert a UTC datetime into local datetime. It isn't as simple as a getutcdate() and getdate() difference because the difference changes depending on what the date is.

CLR 集成也不适合我.

CLR integration isn't an option for me either.

几个月前我为这个问题提出的解决方案是有一个夏令时时间表，用于存储接下来 100 年左右的夏令时开始和结束日，这个解决方案看起来不雅，但转换是快速(简单的查表)

The solution that I had come up with for this problem a few months back was to have a daylight savings time table that stored the beginning and ending daylight savings days for the next 100 or so years, this solution seemed inelegant but conversions were quick (simple table lookup)

推荐答案

创建两个表，然后加入它们以将存储的 GMT 日期转换为本地时间:

Create two tables and then join to them to convert stored GMT dates to local time:

TimeZones     e.g.
---------     ----
TimeZoneId    19
Name          Eastern (GMT -5)
Offset        -5

创建夏令时表并尽可能多地填充它(当地法律一直在变化，因此无法预测未来几年数据会是什么样子)

Create the daylight savings table and populate it with as much information as you can (local laws change all the time so there's no way to predict what the data will look like years in the future)

DaylightSavings
---------------
TimeZoneId    19
BeginDst      3/9/2008 2:00 AM
EndDst        11/2/2008 2:00 AM

像这样加入他们:

inner join  TimeZones       tz on x.TimeZoneId=tz.TimeZoneId
left join   DaylightSavings ds on tz.TimeZoneId=ds.LocalTimeZone 
    and x.TheDateToConvert between ds.BeginDst and ds.EndDst

像这样转换日期:

dateadd(hh, tz.Offset + 
    case when ds.LocalTimeZone is not null 
    then 1 else 0 end, TheDateToConvert)

这篇关于在 SQL 2005 中有效地在 UTC 和本地(即 PST)时间之间转换日期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！