我正在处理一个包含许多以本地时间存储的DateTime值的SQL Server数据库。 （是的，这很不幸！）我们有大约5-10年的数据，这意味着如果该地区遵守夏令时，则该位置的UTC偏移量将根据一年中的时间而更改，并且当然发生这种更改的时间表也可能会更改，例如在2007年美国（其中大部分数据源自）进行了更改。
我的目标是在SQL级别将这些DateTime转换为UTC时间。 除了加载整个Olson或TZ数据库并查询它之外，是否有人有将历史本地时间戳转换为UTC时间的技术？[如果有帮助，方便的是，我们还具有每行的纬度和经度（可用于识别时区）。]
注意：对于实时编写的行，当然可以使用DATEDIFF（Hour，Getutcdate（），GETDATE（））AS UtcOffset的技巧。 问题是将其追溯地应用于在夏令时“障碍”两侧发生的日期。
在SQL Server中将历史本地时间转换为UTC时间
14
- jbeldock
5
编写一个CLR，与http://askgeo.com/接口？即使在2007年之后，印第安纳州在夏令时方面也一直处于混乱状态。这个问题可能会很快变得棘手。 - Anon
谢谢！CLR函数肯定是一个选项——没问题。对于AskGEO，这将需要两个查询：一个用于获取Olson时区字符串，然后第二个（结果不在AskGEO中）用于计算UTC。我会研究一下。 - jbeldock
1如果唯一地理位置数量较少，您可以先将它们总结到一个翻译表中。手动查找每个位置的夏令时开始和结束时间，并输入基本的时区偏移量。然后，您可以相对容易地将翻译表应用于批量数据。 - Ross Presser
3请注意，一般情况下UTC偏移量不必是整数小时。有些地方的UTC偏移量是一个小时加30分钟或45分钟。 - Vladimir Baranov
我的回答会对您有所帮助：https://dev59.com/q2sz5IYBdhLWcg3wOlOh#57710501 - Elliveny
4个回答
4
您可以使用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;
- GiG
0
我过去使用过两种方法。第一种是创建一个 .Net CLR，它接受日期时间和时区，并返回存储在数据中的 UTC 日期时间值。第二个解决方案仅需要适用于有限数量的时区，并涉及创建一个表，其中包含时区 ID、日期从、日期到以及过去日期和未来 20 年的正确 UTC 偏移量。从那里开始，只需简单地加入并应用正确的偏移量即可。
- tkeen
0
这是基于Chris Barlow之前的回答，位于
SQL Server - Convert date field to 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
- jahlife
0
我使用以下代码将本地东部时间转换为协调世界时（因此函数中有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
- MPost
网页内容由stack overflow 提供, 点击上面的可以查看英文原文，
原文链接
原文链接