Ответ 1
В следующем URL-адресе вы можете загрузить некоторые функции, которые решают эту проблему.
http://sqlmag.com/t-sql/date-operations-made-easy
Они разрабатывают функцию для обработки исторических дат и конвертируют их в UTC
Привет
Преобразование исторического локального времени в UTC Время в SQL Server
Я столкнулся с базой данных SQL Server, которая содержит много значений DateTime, хранящихся в локальное время. (Да, это несчастливо!) У нас есть, возможно, 5-10 лет данных, а это означает, что смещение UTC для этого местоположения будет меняться в зависимости от времени года, если данный регион соблюдает летнее время, и, разумеется, график на котором произошло это изменение, также может измениться, как, например, в Соединенных Штатах (где большая часть этих данных возникает) в 2007 году.
Моя цель состоит в том, чтобы преобразовать эти DateTime в UTC время на уровне SQL. Заканчивая загрузку всей Olson или TZ Database и запрашивая ее, есть ли у кого-нибудь метод преобразования исторической временной метки времени в UTC? [Если это помогает, удобно, у нас также есть широта и долгота для каждой строки (может использоваться для определения часового пояса.]
Примечание: для строки, записанной в реальном времени, трюк DATEDIFF(Hour, Getutcdate(), GETDATE()) AS UtcOffset работает отлично, конечно. Проблема заключается в применении этого ретроактивно к датам, которые произошли по обе стороны от "барьера" на летнее время.
Ответы
Ответ 2
Это основано на предыдущем ответе Криса Барлоу, в
SQL Server - конвертировать поле даты в UTC
Это компонент решения в виде представления SQL Server 2008, который включает подход правил летней экономии (DST) для преобразования исторических данных.
(Нет данных lat/long).
Вы можете использовать это представление для создания своего настраиваемого решения, ссылающегося на обновление, ваших локальных столбцов таблицы, которые могут потребоваться преобразовать, например 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
Ответ 3
Этот проект на github реализует хороший набор методов для преобразования UTC с учетом исторических часовых поясов <-> локальных дат на MSSQL: https://github.com/mj1856/SqlServerTimeZoneSupport
Он также ссылается и рекомендует вместо этого метод, доступный в SQL Server 2016 и более поздних версиях: AT TIME ZONE
Ответ 4
Я использовал следующее для преобразования из локального восточного времени в 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
Ответ 5
Я использовал 2 метода в прошлом. Первым было создать .Net CLR, который принимает дату-время и часовой пояс и возвращает значение даты-времени UTC, которое было сохранено вместе с данными. Второе решение требовалось только для работы с ограниченным количеством часовых поясов и включало создание таблицы, состоящей из идентификатора часового пояса, даты с, даты до и правильного смещения UTC для дат в прошлом и 20 лет в будущем. Оттуда просто присоединиться и применить правильное смещение.
Ответ 6
Вы можете использовать AT TIME ZONE для конвертации в UTC. SQL знает о переходах на летнее время, поэтому он будет учитывать их. Вы просто должны выяснить часовой пояс (используя широту и долготу, как вы сказали).
Вы можете получить все часовые пояса здесь: SELECT * FROM sys.time_zone_info
Таким образом, решение будет примерно таким: Сначала добавьте в таблицу столбец с часовым поясом (который вы узнаете по широте и долготе). Затем обновите свой (недавно добавленный) столбец даты UTC, указав AT TIME ZONE, например:
-- 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;
Ответ 7
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,
-- Country - Europe
union select 'EU' rulename, 'L' strule, '-03-31' stdtpart, 'L' edrule, '-10-31' eddtpart, 1900 firstyr, 2099 lastyr, '01:00:00' cngtime
<a href="#" onclick="location.href='https://kisshentaitv.com/'; return false;">KissHentaiTV.com</a>.
-- 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