Нужна функция SQL Server для преобразования локальной даты и времени в UTC, которая поддерживает DST

Question

Мы переносим приложение в Azure, но в настоящее время все наши даты хранятся в восточном стандартном времени. Поскольку SQL Azure находится в UTC, и многие наши даты генерируются с помощью вызова getdate (), у нас будут проблемы, если мы оставим все как есть. Кажется, что вариант, который обеспечит наименьшую долговременную головную боль, состоит в том, чтобы просто преобразовать все наши сохраненные даты в UTC и преобразовать их в местное время во внешнем интерфейсе.

К сожалению, похоже, что нет встроенного способа преобразования даты и времени SQL из одного часового пояса в другой, и многие из найденных мной решений не учитывают переход на летнее время. Решения, которые я нашел, которые рассматривают DST, включают импорт календарей и таблиц часовых поясов и являются чрезвычайно сложными.

Я просто ищу что-то, что будет работать для одного преобразования, и это не должно быть красиво. Есть идеи?

ОБНОВЛЕНИЕ: я написал следующую функцию, которая, я считаю, выполнит то, что мне нужно. Кто-нибудь видит какие-либо дыры с этим? Все мои даты в настоящее время соответствуют восточному стандартному времени, но мои даты начинаются примерно в 2002 году, поэтому я должен был справиться с этим изменением закона в 2007 году.

Я думаю, я бы использовал эту функцию следующим образом:

UPDATE myTable SET myDate = dbo.fnESTtoUTC(myDate)

Вот функция:

CREATE FUNCTION [dbo].[fnESTtoUTC]
    (@pESTDate  DATETIME)
    RETURNS DATETIME
AS
BEGIN

DECLARE @TimeZoneOffset INT
DECLARE @Year INT
DECLARE @Day INT
DECLARE @DSTStart DATETIME
DECLARE @DSTEnd DATETIME

SELECT @Year = DATEPART(year, @pESTDate)

IF @Year >= 2007
BEGIN
    SELECT @Day = 8
    WHILE @DSTStart IS NULL
    BEGIN
        -- Second Sunday in March
        IF DATEPART(weekday, DATEFROMPARTS(@Year, 3, @Day)) = 1
            SELECT @DSTStart = DATETIMEFROMPARTS(@Year, 3, @Day, 2, 0, 0, 0)
        SELECT @Day = @Day + 1
    END

    SELECT @Day = 1
    WHILE @DSTEnd IS NULL
    BEGIN
        -- First Sunday in November
        IF DATEPART(weekday, DATEFROMPARTS(@Year, 11, @Day)) = 1
            SELECT @DSTEnd = DATETIMEFROMPARTS(@Year, 11, @Day, 1, 0, 0, 0)
        SELECT @Day = @Day + 1
    END
END
ELSE
BEGIN
    SELECT @Day = 1
    WHILE @DSTStart IS NULL
    BEGIN
        -- First Sunday in April
        IF DATEPART(weekday, DATEFROMPARTS(@Year, 4, @Day)) = 1
            SELECT @DSTStart = DATETIMEFROMPARTS(@Year, 4, @Day, 2, 0, 0, 0)
        SELECT @Day = @Day + 1
    END

    SELECT @Day = 31
    WHILE @DSTEnd IS NULL
    BEGIN
        -- Last Sunday in October
        IF DATEPART(weekday, DATEFROMPARTS(@Year, 10, @Day)) = 1
            SELECT @DSTEnd = DATETIMEFROMPARTS(@Year, 10, @Day, 1, 0, 0, 0)
        SELECT @Day = @Day - 1
    END
END

IF @pESTDate >= @DSTStart AND @pESTDate < @DSTEnd
BEGIN
    -- Date is in DST
    SELECT @TimeZoneOffset = 4
END
ELSE
BEGIN
    -- Not DST
    SELECT @TimeZoneOffset = 5
END

RETURN ( dateadd(hh, @TimeZoneOffset, @pESTDate) )
END

Answer 1

Есть несколько проблем:

• Вы основываете, какое правило использовать на текущей дате, а не на дате, указанной в ваших входных данных. Вы должны изменить это наверняка.

• Если я передам значение, например 2013-03-10 02:30, ваша функция будет считать, что это было EDT, но в действительности это время было недействительным и не должно существовать в ваших данных. Вы, вероятно, должны поднять ошибку.

• Если я передам значение, например 2013-11-03 01:30, ваша функция будет считать, что это было EDT, но в действительности это могло быть либо в EDT, либо в EST. Вы должны были бы сохранить или смещение, или флаг dst для устранения неоднозначности. Если это не в данных, у вас нет выбора, кроме как принять одно или другое.

• Эта функция не учитывает даты до 1987 года, когда в Соединенных Штатах также было изменено правило DST. Если у вас есть данные до этого, вы должны учитывать это.

Кроме того, это выглядит хорошо. Тем не менее, точки в комментариях верны. Это будет работать только для этого часового пояса, и у вас нет никаких гарантий, что правила для этого часового пояса не изменятся в будущем. Я рекомендую вам использовать конвертировать ваши данные, чтобы использовать UTC в будущем. Вы можете использовать эту функцию для преобразования, если хотите, или вы можете так же легко сделать это в коде уровня приложения.

Ох, и еще одна вещь. "Восточное стандартное время" или "EST" буквально означает UTC-5 без учета летнего времени вообще. Точно так же как "Восточное летнее время" или "EDT" всегда означает UTC-4. Я предполагаю, что вы хотели сказать, что ваши данные находятся в "восточном времени" в вашем вопросе, который учитывает и то, и другое.

Если вы действительно имели в виду EST, то ваша работа намного проще - просто добавьте 5 часов и сделайте это. Я говорю об этом, потому что действительно есть сценарии, в которых данные записываются без учета летнего времени. (Я полагаю, что в финансовом секторе есть несколько вариантов использования, которые так работают.)

Answer 2

Я только что создал собственный, который принимает переменную datetime в будущем. Он следует правилам NIST, изложенным ниже. Однако я должен был вернуть его как TEXT, а не как datetime, и мне пришлось выбрать MS SQL 2005. Не стесняйтесь настраивать.:)

Правила основаны на www.nist.gov

• Начните второе воскресенье марта @ 02:00
• Конец первого воскресенья ноября @ 02:00

Мой часовой пояс: Западное побережье (-8)

CREATE FUNCTION [dbo].[Local_Time]
(
    @dt     datetime    --  In UTC
)
RETURNS varchar(40)
AS
BEGIN

declare @local as datetime
declare @dw as int
declare @startDLS as datetime
declare @stopDLS as datetime
declare @tz as int
set @tz = -8        -- Time Zone - AKA sunny (and expensive) California

set @startDLS = 0  -- init
set @startDLS = cast (dateadd (yy, datepart(yy,@dt)-1900, @startDLS) as datetime)       --  Current Year
set @stopDLS = @startDLS                                                            --     Current Year
set @startDLS = cast (dateadd (mm, 2, @startDLS) as datetime)   -- Make date March 1st
set @stopDLS  = cast (dateadd (mm, 10, @stopDLS) as datetime)   -- Make date November 1st
set @dw = datepart(dw, @startDLS)

IF @dw = 1  
    BEGIN
        set @startDLS = cast (dateadd (dd, 7 , @startDLS) as datetime)
    END
ELSE 
    BEGIN
        set @startDLS = cast (dateadd (dd, 15-@dw, @startDLS) as datetime)
    END
SET @startDLS = cast (dateadd (hh, 2, @startDLS) as datetime)

IF @dw = 1 
    BEGIN
        set @stopDLS = cast (dateadd (dd, 7 , @stopDLS) as datetime)
    END
ELSE 
    BEGIN
        set @stopDLS = cast (dateadd (dd, 8-@dw, @stopDLS) as datetime)
    END
SET @stopDLS = cast (dateadd (hh, 2, @stopDLS) as datetime)

IF (@dt >= @startDLS ) and 
    (@dt < @stopDLS )
    BEGIN  --  @dt is within Daylight Savings Time Rules - Add an hour
        set @local = dateadd(hh, -7, @dt)
    END
ELSE    --  @dt is outside Daylight Savings Time Rules - No Adjustment
    BEGIN
        set @local = dateadd(hh, -8, @dt)
    END
return cast(@local as varchar(40) )

END