我正面对一个SQL Server数据库，其中包含许多DateTime值存储在本地时间。(是的，这很不幸!)我们可能有5-10年的数据，这意味着这个位置的UTC偏移量将变化取决于一年中的时间，如果有问题的地区遵守夏令时，当然，发生变化的时间表也可能发生变化，例如2007年在美国(大多数这些数据的来源)。
我的目标是在SQL级别将这些DateTime s转换为UTC时间。缺少加载整个Olson或TZ数据库并查询它，有没有人有一个技术转换历史本地时间戳到UTC时间?[如果它有帮助，方便的话，我们碰巧也有每行的经纬度(可以用来识别时区)。]
注意:对于实时编写的行，DATEDIFF(Hour, Getutcdate(), GETDATE()) AS UtcOffset的技巧当然可以很好地工作。问题是将此回溯应用于发生在日光节约时间"屏障"两侧的日期。
您可以使用AT TIME ZONE转换为UTC。SQL知道夏令时的转换，因此它将对此进行解释。你只需要计算出时区(使用经纬度，正如你所说的)。
你可以从这里获得所有时区:SELECT * FROM sys.time_zone_info
那么解决方案将是这样的:首先，向表中添加一个带有时区的列(使用纬度和经度来确定)。然后用AT TIME ZONE更新(新添加的)UTC日期列，例如:
-- some sample data to play with
CREATE TABLE #YourTable
(
LocalDateTime DATETIME,
[UtcDateTime] DATETIMEOFFSET,
TimeZoneName VARCHAR(100)
);
INSERT INTO #YourTable
(
LocalDateTime,
TimeZoneName
)
VALUES
('20150101', 'Alaskan Standard Time'),
('20150101', 'US Mountain Standard Time'),
('20190701', 'Alaskan Standard Time'),
('20190701', 'US Mountain Standard Time');
-- convert to UTC
UPDATE #YourTable
SET [UtcDateTime] = LocalDateTime AT TIME ZONE TimeZoneName AT TIME ZONE 'UTC';
-- check results
SELECT * FROM #YourTable;
这是基于Chris Barlow之前的回答，在
SQL Server -将日期字段转换为UTC
这是一个SQL Server 2008视图形式的解决方案组件，其中包括用于历史数据转换的夏令时(DST)规则方法。
(不需要后期/长期数据)
您可以使用此视图创建您的自定义解决方案引用，用于更新可能需要转换的本地表列，如dbo.mytable.created_date。
下面引用了一些关于使用视图的注释，其中有趣的是"示例用法-用于历史数据转换"一节:
--
-- DATETIME VS. DATETIMEOFFSET
--
-- WHERE, t = '2016-12-13 04:32:00'
--
declare
@Sydney DATETIME
set
@Sydney = '2016-12-13 04:32:00'
select
Sydney = @Sydney
declare
@Sydney_UTC DATETIMEOFFSET
set
@Sydney_UTC = '2016-12-13 04:32:00.6427663 +10:00'
select
Sydney_UTC = @Sydney_UTC
declare
@NewYork DATETIME
set
@NewYork = '2016-12-13 04:32:00:34'
select
NewYork = @NewYork
declare
@NewYork_UTC DATETIMEOFFSET
set
@NewYork_UTC = '2016-12-13 04:32:00.6427663 -04:00'
select
NewYork_UTC = @NewYork_UTC
select
DATEDIFF(hh, @Sydney, @NewYork) as DIFF_DATETIME
select
DATEDIFF(hh, @Sydney_UTC, @NewYork_UTC) as DIFF_DATETIMEOFFSET
--
-- LOCAL UTC OFFSET FOR REAL-TIME DATA TODAY
--
select
DATEDIFF( Hour, GETUTCDATE(), GETDATE() ) AS UtcOffset
--
-- LOCAL UTC DATE FOR REAL-TIME DATA TODAY - EASTERN STANDARD EXAMPLE
--
select
convert( datetimeoffset( 5 ), GETDATE(), 120 )
--
-- EXAMPLE USAGE -
--
select
*
from
vw_datetime__dst__timezone
--
-- EXAMPLE USAGE - FOR HISTORICAL DATA CONVERSION - EASTERN STANDARD
--
select
created_date,
isnull( dst.zone, 'NO TZ' ) as zone,
isnull(
case
when created_date >= dstlow and
created_date < dsthigh
then dst.daylight
else dst.standard
end,
'NO OFFSET'
) as zone_offsettime,
TODATETIMEOFFSET(
created_date,
case
when created_date >= dstlow and
created_date < dsthigh
then dst.daylight
else dst.standard
end
) as zone_time,
SWITCHOFFSET(
TODATETIMEOFFSET(
created_date,
case
when created_date >= dstlow and
created_date < dsthigh
then dst.daylight
else dst.standard
end
),
'+00:00' -- parameterize?
) as utc_time
from
(
select GETDATE() as created_date
union
select SYSDATETIMEOFFSET() as created_date
union
select '2017-01-01 15:20:24.653' as created_date
) DYNAMIC_temp_table
left outer join vw_datetime__dst__timezone dst on
created_date between yrstart and yrend and
dst.zone = 'ET'
order by
created_date
——视图SQL:
drop view
vw_datetime__dst__timezone
go
create view
vw_datetime__dst__timezone
as
select
yr,
zone,
standard,
daylight,
rulename,
strule,
edrule,
yrstart,
yrend,
dateadd(day, (stdowref + stweekadd), stmonthref) dstlow,
dateadd(day, (eddowref + edweekadd), edmonthref) dsthigh
from (
select
yrs.yr,
timezone.zone,
timezone.standard,
timezone.daylight,
timezone.rulename,
dst_rule.strule,
dst_rule.edrule,
yrs.yr + '-01-01 00:00:00' yrstart,
yrs.yr + '-12-31 23:59:59' yrend,
yrs.yr + dst_rule.stdtpart + ' ' + dst_rule.cngtime stmonthref,
yrs.yr + dst_rule.eddtpart + ' ' + dst_rule.cngtime edmonthref,
case
when dst_rule.strule in ('1', '2', '3')
then
case
when datepart(dw, yrs.yr + dst_rule.stdtpart) = '1'
then 0
else 8 - datepart(dw, yrs.yr + dst_rule.stdtpart)
end
else (datepart(dw, yrs.yr + dst_rule.stdtpart) - 1) * -1
end as stdowref,
case
when dst_rule.edrule in ('1', '2', '3')
then
case
when datepart(dw, yrs.yr + dst_rule.eddtpart) = '1'
then 0
else 8 - datepart(dw, yrs.yr + dst_rule.eddtpart)
end
else (datepart(dw, yrs.yr + dst_rule.eddtpart) - 1) * -1
end as eddowref,
datename(dw, yrs.yr + dst_rule.stdtpart) as stdow,
datename(dw, yrs.yr + dst_rule.eddtpart) as eddow,
case
when dst_rule.strule in ('1', '2', '3')
then (7 * CAST(dst_rule.strule AS Integer)) - 7
else 0
end as stweekadd,
case
when dst_rule.edrule in ('1', '2', '3')
then (7 * CAST(dst_rule.edrule AS Integer)) - 7
else 0
end as edweekadd
from (
select '1900' yr
union select '1901' yr
union select '1902' yr
union select '1903' yr
union select '1904' yr
union select '1905' yr
union select '1906' yr
union select '1907' yr
union select '1908' yr
union select '1909' yr
union select '1910' yr
union select '1911' yr
union select '1912' yr
union select '1913' yr
union select '1914' yr
union select '1915' yr
union select '1916' yr
union select '1917' yr
union select '1918' yr
union select '1919' yr
union select '1920' yr
union select '1921' yr
union select '1922' yr
union select '1923' yr
union select '1924' yr
union select '1925' yr
union select '1926' yr
union select '1927' yr
union select '1928' yr
union select '1929' yr
union select '1930' yr
union select '1931' yr
union select '1932' yr
union select '1933' yr
union select '1934' yr
union select '1935' yr
union select '1936' yr
union select '1937' yr
union select '1938' yr
union select '1939' yr
union select '1940' yr
union select '1941' yr
union select '1942' yr
union select '1943' yr
union select '1944' yr
union select '1945' yr
union select '1946' yr
union select '1947' yr
union select '1948' yr
union select '1949' yr
union select '1950' yr
union select '1951' yr
union select '1952' yr
union select '1953' yr
union select '1954' yr
union select '1955' yr
union select '1956' yr
union select '1957' yr
union select '1958' yr
union select '1959' yr
union select '1960' yr
union select '1961' yr
union select '1962' yr
union select '1963' yr
union select '1964' yr
union select '1965' yr
union select '1966' yr
union select '1967' yr
union select '1968' yr
union select '1969' yr
union select '1970' yr
union select '1971' yr
union select '1972' yr
union select '1973' yr
union select '1974' yr
union select '1975' yr
union select '1976' yr
union select '1977' yr
union select '1978' yr
union select '1979' yr
union select '1980' yr
union select '1981' yr
union select '1982' yr
union select '1983' yr
union select '1984' yr
union select '1985' yr
union select '1986' yr
union select '1987' yr
union select '1988' yr
union select '1989' yr
union select '1990' yr
union select '1991' yr
union select '1992' yr
union select '1993' yr
union select '1994' yr
union select '1995' yr
union select '1996' yr
union select '1997' yr
union select '1998' yr
union select '1999' yr
union select '2000' yr
union select '2001' yr
union select '2002' yr
union select '2003' yr
union select '2004' yr
union select '2005' yr
union select '2006' yr -- OLD US RULES
union select '2007' yr
union select '2008' yr
union select '2009' yr
union select '2010' yr
union select '2011' yr
union select '2012' yr
union select '2013' yr
union select '2014' yr
union select '2015' yr
union select '2016' yr
union select '2017' yr
union select '2018' yr
union select '2018' yr
union select '2020' yr
union select '2021' yr
union select '2022' yr
union select '2023' yr
union select '2024' yr
union select '2025' yr
union select '2026' yr
union select '2027' yr
union select '2028' yr
union select '2029' yr
union select '2030' yr
union select '2031' yr
union select '2032' yr
union select '2033' yr
union select '2034' yr
union select '2035' yr
union select '2036' yr
union select '2037' yr
union select '2038' yr
union select '2039' yr
union select '2040' yr
union select '2041' yr
union select '2042' yr
union select '2043' yr
union select '2044' yr
union select '2045' yr
union select '2046' yr
union select '2047' yr
union select '2048' yr
union select '2049' yr
union select '2050' yr
union select '2051' yr
union select '2052' yr
union select '2053' yr
union select '2054' yr
union select '2055' yr
union select '2056' yr
union select '2057' yr
union select '2058' yr
union select '2059' yr
union select '2060' yr
union select '2061' yr
union select '2062' yr
union select '2063' yr
union select '2064' yr
union select '2065' yr
union select '2066' yr
union select '2067' yr
union select '2068' yr
union select '2069' yr
union select '2070' yr
union select '2071' yr
union select '2072' yr
union select '2073' yr
union select '2074' yr
union select '2075' yr
union select '2076' yr
union select '2077' yr
union select '2078' yr
union select '2079' yr
union select '2080' yr
union select '2081' yr
union select '2082' yr
union select '2083' yr
union select '2084' yr
union select '2085' yr
union select '2086' yr
union select '2087' yr
union select '2088' yr
union select '2089' yr
union select '2090' yr
union select '2091' yr
union select '2092' yr
union select '2093' yr
union select '2094' yr
union select '2095' yr
union select '2096' yr
union select '2097' yr
union select '2098' yr
union select '2099' yr
) yrs
cross join (
-- Dynamic, hardcoded table of timezone-based, daylight savings time (DST) rules
-- -- TIMEZONE
select 'UTC' zone, '+00:00' standard, '+01:00' daylight, 'UTC' rulename -- UTC - STAGING ONLY - this line is not accurate
union select 'CET' zone, '+01:00' standard, '+02:00' daylight, 'EU' rulename -- Centeral Europe
union select 'ET' zone, '-05:00' standard, '-04:00' daylight, 'US' rulename -- Eastern Time
union select 'CT' zone, '-06:00' standard, '-05:00' daylight, 'US' rulename -- Central Time
union select 'MT' zone, '-07:00' standard, '-06:00' daylight, 'US' rulename -- Mountain Time
union select 'PT' zone, '-08:00' standard, '-07:00' daylight, 'US' rulename -- Pacific Time
) timezone
join (
-- Dynamic, hardcoded table of country-based, daylight savings time (DST) rules
select 'UTC' rulename, 'L' strule, '-03-31' stdtpart, 'L' edrule, '-10-31' eddtpart, 1900 firstyr, 2099 lastyr, '01:00:00' cngtime
-- Country - Europe
union select 'EU' rulename, 'L' strule, '-03-31' stdtpart, 'L' edrule, '-10-31' eddtpart, 1900 firstyr, 2099 lastyr, '01:00:00' cngtime
-- Country - US
union select 'US' rulename, '1' strule, '-04-01' stdtpart, 'L' edrule, '-10-31' eddtpart, 1900 firstyr, 2006 lastyr, '02:00:00' cngtime
union select 'US' rulename, '2' strule, '-03-01' stdtpart, '1' edrule, '-11-01' eddtpart, 2007 firstyr, 2099 lastyr, '02:00:00' cngtime
) dst_rule on
dst_rule.rulename = timezone.rulename and
datepart( year, yrs.yr ) between firstyr and lastyr
) dst_dates
go
我使用以下代码将当地东部时间转换为UTC(因此函数中的固定值为4和5)。如果您有2007年以前的值，那么您实际上也需要修改下面的udf_IsInDST以适应它。
CREATE FUNCTION [dbo].[udf_ConvertTimeLocalToUTC](@dt DATETIME)
RETURNS DATETIME
AS
BEGIN
SET @dt = DATEADD(HOUR, CASE WHEN [dbo].udf_IsInDST(@dt) = 1 THEN 4 ELSE 5 END, @dt)
RETURN @dt
END
GO
CREATE FUNCTION [dbo].[udf_IsInDST](@dt DATETIME)
RETURNS BIT
AS
BEGIN
DECLARE @returnValue BIT = 0
DECLARE @mm INT = DATEPART(MONTH, @dt)
DECLARE @dd INT = DATEPART(DAY, @dt)
DECLARE @dow INT = DATEPART(dw, @dt) -- 1 = sun
DECLARE @hr INT = DATEPART(HOUR, @dt)
SET @returnValue =
CASE WHEN @mm > 3 AND @mm < 11 THEN 1
WHEN @mm = 3 THEN
CASE WHEN @dd < 8 THEN 0
WHEN @dd >= 8 AND @dd <= 14 THEN (CASE WHEN @dow = 1 THEN (CASE WHEN @hr >= 2 THEN 1 ELSE 0 END) ELSE (CASE WHEN @dd - @dow >= 7 THEN 1 ELSE 0 END) END)
ELSE 1
END
WHEN @mm = 11 THEN
CASE WHEN @dd < 7 THEN (CASE WHEN @dow = 1 THEN (CASE WHEN @hr < 2 THEN 1 ELSE 0 END) ELSE (CASE WHEN @dow > @dd THEN 1 ELSE 0 END) END)
ELSE 0
END
ELSE 0
END;
RETURN @returnValue
END
GO
我过去用过两种方法。第一种方法是创建一个。net CLR，它接受日期时间和时区，并返回与数据一起存储的UTC日期时间值。第二种解决方案只需要在有限数量的时区中工作，并且需要创建一个由时区ID、日期起始、日期到以及过去和未来20年日期的正确UTC偏移量组成的表。从这里可以简单地连接并应用正确的偏移量。