Как вычислить локальное datetime из utc datetime в tsql (sql 2005)?

Я хочу зациклиться на период времени в tsql и напечатать utc datetimes и наш локальный вариант. Мы живем в UTC +1, поэтому я могу легко добавить 1 час, но в летнее время мы живем в UTC +2.

В С# я могу создать дату-время и использовать метод для запроса варианта UTC и наоборот.

До сих пор у меня есть это:

declare @counter int
declare @localdate datetime
declare @utcdate datetime
 set @counter = 0
 while @counter < 100
 begin
   set @counter = @counter + 1
   print 'The counter is ' + cast(@counter as char)
  set @utcdate  = DATEADD(day,@counter,GETUTCDATE())
  --set @localdate = ????

  print  @localdate  
  print @utcdate
 end

Ответы

Ответ 1

Предполагая, что вы используете SQL 2005 вверх, вы можете разработать функцию CLR SQL, чтобы получить дату UTC и преобразовать в локальную дату.

Эта ссылка является инструкцией MSDN о том, как вы можете создать скалярный UDF в С#.

Создайте функцию SQL по строкам

[SqlFunction()]
public static SqlDateTime ConvertUtcToLocal(SqlDateTime utcDate) 
{
    // over to you to convert SqlDateTime to DateTime, specify Kind
    // as UTC, convert to local time, and convert back to SqlDateTime
}

Затем ваш образец выше станет

set @localdate = dbo.ConvertUtcToLocal(@utcdate)

SQL CLR имеет свои накладные расходы с точки зрения развертывания, но я чувствую, что такие случаи, когда они подходят, в лучшем случае.

Ответ 2

Я ждал 5 лет для более элегантного решения, но поскольку он не появился, я опубликую то, что я использовал до сих пор...

CREATE FUNCTION [dbo].[UDTToLocalTime](@UDT AS DATETIME)  
RETURNS DATETIME
AS
BEGIN 
--====================================================
--Set the Timezone Offset (NOT During DST [Daylight Saving Time])
--====================================================
DECLARE @Offset AS SMALLINT
SET @Offset = -5

--====================================================
--Figure out the Offset Datetime
--====================================================
DECLARE @LocalDate AS DATETIME
SET @LocalDate = DATEADD(hh, @Offset, @UDT)

--====================================================
--Figure out the DST Offset for the UDT Datetime
--====================================================
DECLARE @DaylightSavingOffset AS SMALLINT
DECLARE @Year as SMALLINT
DECLARE @DSTStartDate AS DATETIME
DECLARE @DSTEndDate AS DATETIME
--Get Year
SET @Year = YEAR(@LocalDate)

--Get First Possible DST StartDay
IF (@Year > 2006) SET @DSTStartDate = CAST(@Year AS CHAR(4)) + '-03-08 02:00:00'
ELSE              SET @DSTStartDate = CAST(@Year AS CHAR(4)) + '-04-01 02:00:00'
--Get DST StartDate 
WHILE (DATENAME(dw, @DSTStartDate) <> 'sunday') SET @DSTStartDate = DATEADD(day, 1,@DSTStartDate)


--Get First Possible DST EndDate
IF (@Year > 2006) SET @DSTEndDate = CAST(@Year AS CHAR(4)) + '-11-01 02:00:00'
ELSE              SET @DSTEndDate = CAST(@Year AS CHAR(4)) + '-10-25 02:00:00'
--Get DST EndDate 
WHILE (DATENAME(dw, @DSTEndDate) <> 'sunday') SET @DSTEndDate = DATEADD(day,1,@DSTEndDate)

--Get DaylightSavingOffset
SET @DaylightSavingOffset = CASE WHEN @LocalDate BETWEEN @DSTStartDate AND @DSTEndDate THEN 1 ELSE 0 END

--====================================================
--Finally add the DST Offset 
--====================================================
RETURN DATEADD(hh, @DaylightSavingOffset, @LocalDate)
END



GO

Примечания:

Это для североамериканских серверов, которые наблюдают на летнее время. Пожалуйста, измените переменную @Offest на смещение временной зоны сервера, на котором запущена функция SQL (хотя не соблюдается время перехода на летнее время)...

--====================================================
--Set the Timezone Offset (NOT During DST [Daylight Saving Time])
--====================================================
DECLARE @Offset AS SMALLINT
SET @Offset = -5

Поскольку правила DST меняют их здесь...

--Get First Possible DST StartDay
IF (@Year > 2006) SET @DSTStartDate = CAST(@Year AS CHAR(4)) + '-03-08 02:00:00'
ELSE              SET @DSTStartDate = CAST(@Year AS CHAR(4)) + '-04-01 02:00:00'
--Get DST StartDate 
WHILE (DATENAME(dw, @DSTStartDate) <> 'sunday') SET @DSTStartDate = DATEADD(day, 1,@DSTStartDate)


--Get First Possible DST EndDate
IF (@Year > 2006) SET @DSTEndDate = CAST(@Year AS CHAR(4)) + '-11-01 02:00:00'
ELSE              SET @DSTEndDate = CAST(@Year AS CHAR(4)) + '-10-25 02:00:00'
--Get DST EndDate 
WHILE (DATENAME(dw, @DSTEndDate) <> 'sunday') SET @DSTEndDate = DATEADD(day,1,@DSTEndDate)

Приветствия,

Ответ 3

Это решение кажется слишком очевидным.

Если вы можете получить UTC Date с GETUTCDATE(), и вы можете получить свою локальную дату с помощью GETDATE(), у вас есть смещение, которое вы можете применить для любого datetime

SELECT DATEADD(hh, DATEPART(hh, GETDATE() - GETUTCDATE()) - 24, GETUTCDATE())

это должно вернуть локальное время выполнения запроса,

SELECT DATEADD(hh, DATEPART(hh, GETDATE() - GETUTCDATE()) - 24, N'1/14/2011 7:00:00'  )

это вернется 2011-01-14 02: 00: 00.000, потому что я в UTC +5

Если мне что-то не хватает?

Ответ 4

Вы можете использовать мой проект поддержки часовых поясов SQL Server для преобразования между стандартными часовыми поясами IANA, как указано здесь.

Пример:

SELECT Tzdb.UtcToLocal('2015-07-01 00:00:00', 'America/Los_Angeles')

Ответ 5

GETUTCDATE() просто дает вам текущее время в UTC, любой DATEADD(), который вы делаете с этим значением, не будет включать сдвиги времени на летнее время.

Лучше всего построить собственную таблицу преобразования UTC или просто использовать что-то вроде этого:

http://www.codeproject.com/KB/database/ConvertUTCToLocal.aspx

Ответ 6

Вот функция (снова US ONLY), но она немного более гибкая. Он преобразует дату UTC в локальное время сервера. Он начинается с корректировки даты назначения на основе текущего смещения, а затем корректируется в зависимости от разницы текущего смещения и смещения даты назначения.

CREATE FUNCTION [dbo].[fnGetServerTimeFromUTC]
(
    @AppointmentDate AS DATETIME, 
    @DateTimeOffset DATETIMEOFFSET
)
RETURNS DATETIME
AS
BEGIN
    --DECLARE @AppointmentDate DATETIME;
    --SET @AppointmentDate = '2016-12-01 12:00:00'; SELECT @AppointmentDate;

    --Get DateTimeOffset from Server
    --DECLARE @DateTimeOffset; SET @DateTimeOffset = SYSDATETIMEOFFSET();
    DECLARE @DateTimeOffsetStr NVARCHAR(34) = @DateTimeOffset;

    --Set a standard DatePart value for Sunday (server configuration agnostic)
    DECLARE @dp_Sunday INT = 7 - @@DATEFIRST + 1;

    --2006 DST Start First Sunday in April (earliest is 04-01) Ends Last Sunday in October (earliest is 10-25)
    --2007 DST Start Second Sunday March (earliest is 03-08) Ends First Sunday Nov (earliest is 11-01)
    DECLARE @Start2006 NVARCHAR(6) = '04-01-';
    DECLARE @End2006 NVARCHAR(6) = '10-25-';
    DECLARE @Start2007 NVARCHAR(6) = '03-08-';
    DECLARE @End2007 NVARCHAR(6) = '11-01-';

    DECLARE @ServerDST SMALLINT = 0;
    DECLARE @ApptDST SMALLINT = 0;
    DECLARE @Start DATETIME;
    DECLARE @End DATETIME;

    DECLARE @CurrentMinuteOffset INT; 

    DECLARE @str_Year NVARCHAR(4) = LEFT(@DateTimeOffsetStr,4);
    DECLARE @Year INT = CONVERT(INT, @str_Year);

    SET @CurrentMinuteOffset = CONVERT(INT, SUBSTRING(@DateTimeOffsetStr,29,3)) * 60 + CONVERT(INT, SUBSTRING(@DateTimeOffsetStr,33,2)); --Hours + Minutes

    --Determine DST Range for Server Offset
    SET @Start = CASE 
        WHEN @Year <= 2006 THEN CONVERT(DATETIME, @Start2006 + @str_Year + ' 02:00:00')
        ELSE CONVERT(DATETIME, @Start2007 + @str_Year + ' 02:00:00')
        END;
    WHILE @dp_Sunday <> DATEPART(WEEKDAY, @Start) BEGIN
        SET @Start = DATEADD(DAY, 1, @Start)
    END;

    SET @End = CASE 
        WHEN @Year <= 2006 THEN CONVERT(DATETIME, @End2006 + @str_Year + ' 02:00:00')
        ELSE CONVERT(DATETIME, @End2007 + @str_Year + ' 02:00:00')
        END;
    WHILE @dp_Sunday <> DATEPART(WEEKDAY, @End) BEGIN
        SET @End = DATEADD(DAY, 1, @End)
    END;

    --Determine Current Offset based on Year
    IF @DateTimeOffset >= @Start AND @DateTimeOffset < @End SET @ServerDST = 1;

    --Determine DST status of Appointment Date
    SET @Year = YEAR(@AppointmentDate);

    SET @Start = CASE 
        WHEN @Year <= 2006 THEN CONVERT(DATETIME, @Start2006 + @str_Year + ' 02:00:00')
        ELSE CONVERT(DATETIME, @Start2007 + @str_Year + ' 02:00:00')
        END;
    WHILE @dp_Sunday <> DATEPART(WEEKDAY, @Start) BEGIN
        SET @Start = DATEADD(DAY, 1, @Start)
    END;

    SET @End = CASE 
        WHEN @Year <= 2006 THEN CONVERT(DATETIME, @End2006 + @str_Year + ' 02:00:00')
        ELSE CONVERT(DATETIME, @End2007 + @str_Year + ' 02:00:00')
        END;
    WHILE @dp_Sunday <> DATEPART(WEEKDAY, @End) BEGIN
        SET @End = DATEADD(DAY, 1, @End)
    END;

    --Determine Appointment Offset based on Year
    IF @AppointmentDate >= @Start AND @AppointmentDate < @End SET @ApptDST = 1;

    SET @AppointmentDate = DATEADD(MINUTE, @CurrentMinuteOffset + 60 * (@ApptDST - @ServerDST), @AppointmentDate)

    RETURN @AppointmentDate
END
GO

Ответ 7

Ответ Бобмана близок, но есть пара ошибок: 1) Вы должны сравнить местное время DAYLIGHT (вместо локального времени STANDARD) с датой перехода на летнее время. 2) SQL BETWEEN является Inclusive, поэтому вы должны сравнивать с помощью " >= и <" вместо МЕЖДУ.

Вот рабочая модифицированная версия и некоторые тестовые примеры: (Опять же, это работает только для США)

-- Test cases:
-- select dbo.fn_utc_to_est_date('2016-03-13 06:59:00.000') -- -> 2016-03-13 01:59:00.000 (Eastern Standard Time)
-- select dbo.fn_utc_to_est_date('2016-03-13 07:00:00.000') -- -> 2016-03-13 03:00:00.000 (Eastern Daylight Time)
-- select dbo.fn_utc_to_est_date('2016-11-06 05:59:00.000') -- -> 2016-11-06 01:59:00.000 (Eastern Daylight Time)
-- select dbo.fn_utc_to_est_date('2016-11-06 06:00:00.000') -- -> 2016-11-06 01:00:00.000 (Eastern Standard Time)
CREATE FUNCTION [dbo].[fn_utc_to_est_date]
(
    @utc datetime
)
RETURNS datetime
as
begin
    -- set offset in standard time (WITHOUT daylight saving time)
    declare @offset smallint
    set @offset = -5  --EST

    declare @localStandardTime datetime
    SET @localStandardTime = dateadd(hh, @offset, @utc)

    -- DST in USA starts on the second sunday of march and ends on the first sunday of november.
    -- DST was extended beginning in 2007:
    --   https://en.wikipedia.org/wiki/Daylight_saving_time_in_the_United_States#Second_extension_.282005.29
    -- If laws/rules change, obviously the below code needs to be updated.

    declare @dstStartDate datetime,
            @dstEndDate datetime,
            @year int
    set @year = datepart(year, @localStandardTime)

    -- get the first possible DST start day
    if (@year > 2006) set @dstStartDate = cast(@year as char(4)) + '-03-08 02:00:00'
    else              set @dstStartDate = cast(@year as char(4)) + '-04-01 02:00:00'
    while ((datepart(weekday,@dstStartDate) != 1)) begin --while not sunday
        set @dstStartDate = dateadd(day, 1, @dstStartDate)
    end

    -- get the first possible DST end day
    if (@year > 2006) set @dstEndDate = cast(@year as char(4)) + '-11-01 02:00:00'
    else              set @dstEndDate = cast(@year as char(4)) + '-10-25 02:00:00'
    while ((datepart(weekday,@dstEndDate) != 1)) begin --while not sunday
        set @dstEndDate = dateadd(day, 1, @dstEndDate)
    end

    declare @localTimeFinal datetime,
            @localTimeCompare datetime
    -- if local date is same day as @dstEndDate day,
    -- we must compare the local DAYLIGHT time to the @dstEndDate (otherwise we compare using local STANDARD time).
    -- See: http://www.timeanddate.com/time/change/usa?year=2016
    if (datepart(month,@localStandardTime) = datepart(month,@dstEndDate)
            and datepart(day,@localStandardTime) = datepart(day,@dstEndDate)) begin
        set @localTimeCompare = dateadd(hour, 1, @localStandardTime)
    end
    else begin
        set @localTimeCompare = @localStandardTime
    end

    set @localTimeFinal = @localStandardTime

    -- check for DST
    if (@localTimeCompare >= @dstStartDate and @localTimeCompare < @dstEndDate) begin
        set @localTimeFinal = dateadd(hour, 1, @localTimeFinal)
    end

    return @localTimeFinal
end

Ответ 8

Хотя в заголовке вопроса упоминается SQL Server 2005, вопрос помечен как SQL Server в целом. Для SQL Server 2016 и более поздних версий вы можете использовать:

SELECT yourUtcDateTime AT TIME ZONE 'Mountain Standard Time'

Список часовых поясов доступен с помощью SELECT * FROM sys.time_zone_info

Ответ 9

Для тех, кто застрял в SQL Server 2005 и не хочет или не может использовать udf - и, в частности, за пределами США, я использовал подход @Bobman и обобщил его. Следующее будет работать в США, Европе, Новой Зеландии и Австралии, с оговоркой, что не все австралийские государства наблюдают за летними днями, даже в штатах, которые находятся в том же "базовом" часовом поясе. Также легко добавить DST-правила, которые еще не поддерживаются, просто добавьте строку к значениям @calculation.

-- =============================================
-- Author:      Herman Scheele
-- Create date: 20-08-2016
-- Description: Convert UTC datetime to local datetime
--              based on server time-distance from utc.
-- =============================================
create function dbo.UTCToLocalDatetime(@UTCDatetime datetime)
returns datetime as begin
    declare @LocalDatetime datetime, @DSTstart datetime, @DSTend datetime

    declare @calculation table (
        frm smallint,
        til smallint,
        since smallint,
        firstPossibleStart datetime,-- Put both of these with local non-DST time!
          firstPossibleEnd datetime -- (In Europe we turn back the clock from 3 AM to 2 AM, which means it happens 2 AM non-DST time)
    )

    insert into @calculation
    values
        (-9, -2, 1967, '1900-04-24 02:00', '1900-10-25 01:00'), -- USA first DST implementation
        (-9, -2, 1987, '1900-04-01 02:00', '1900-10-25 01:00'), -- USA first DST extension
        (-9, -2, 2007, '1900-03-08 02:00', '1900-11-01 01:00'), -- USA second DST extension
        (-1,  3, 1900, '1900-03-25 02:00', '1900-10-25 02:00'), -- Europe
        (9.5,11, 1971, '1900-10-01 02:00', '1900-04-01 02:00'), -- Australia (not all Aus states in this time-zone have DST though)
        (12, 13, 1974, '1900-09-24 02:00', '1900-04-01 02:00')  -- New Zealand

    select top 1    -- Determine if it is DST /right here, right now/ (regardless of input datetime)
        @DSTstart = dateadd(year, datepart(year, getdate())-1900, firstPossibleStart),          -- Grab first possible Start and End of DST period
        @DSTend   = dateadd(year, datepart(year, getdate())-1900, firstPossibleEnd),            
        @DSTstart = dateadd(day, 6 - (datepart(dw, @DSTstart) + @@datefirst - 2) % 7, @DSTstart),-- Shift Start and End of DST to first sunday
        @DSTend   = dateadd(day, 6 - (datepart(dw, @DSTend) + @@datefirst - 2) % 7, @DSTend),
        @LocalDatetime = dateadd(hour, datediff(hour, getutcdate(), getdate()), @UTCDatetime),  -- Add hours to current input datetime (including possible DST hour)
        @LocalDatetime = case
                when frm < til and getdate() >= @DSTstart and getdate() < @DSTend               -- If it is currently DST then we just erroneously added an hour above,
                  or frm > til and (getdate() >= @DSTstart or getdate() < @DSTend)              -- substract 1 hour to get input datetime in current non-DST timezone,
                    then dateadd(hour, -1, @LocalDatetime)                                      -- regardless of whether it is DST on the date of the input datetime
                else @LocalDatetime
            end
    from @calculation
    where datediff(minute, getutcdate(), getdate()) between frm * 60 and til * 60
      and datepart(year, getdate()) >= since
    order by since desc

    select top 1    -- Determine if it was/will be DST on the date of the input datetime in a similar fashion
        @DSTstart = dateadd(year, datepart(year, @LocalDatetime)-1900, firstPossibleStart),
        @DSTend   = dateadd(year, datepart(year, @LocalDatetime)-1900, firstPossibleEnd),
        @DSTstart = dateadd(day, 6 - (datepart(dw, @DSTstart) + @@datefirst - 2) % 7, @DSTstart),
        @DSTend   = dateadd(day, 6 - (datepart(dw, @DSTend) + @@datefirst - 2) % 7, @DSTend),
        @LocalDatetime = case
                when frm < til and @LocalDatetime >= @DSTstart and @LocalDatetime < @DSTend     -- If it would be DST on the date of the input datetime,
                  or frm > til and (@LocalDatetime >= @DSTstart or @LocalDatetime < @DSTend)    -- add this hour to the input datetime.
                    then dateadd(hour, 1, @LocalDatetime)
                else @LocalDatetime
            end
    from @calculation
    where datediff(minute, getutcdate(), getdate()) between frm * 60 and til * 60
      and datepart(year, @LocalDatetime) >= since
    order by since desc

    return @LocalDatetime
end

Эта функция рассматривает разницу между локальным и utc-временем в момент его запуска, чтобы определить, какие правила DST применяются. Затем он знает, использует ли datediff(hour, getutcdate(), getdate()) время DST или нет и вычитает его, если это произойдет. Затем он определяет, был ли он или будет DST на дату ввода UTC datetime, и если это так добавляет час DST.

Это происходит с одной причудой, которая заключается в том, что в течение последнего часа DST и первого часа не-DST функция не имеет способа определить, что она есть, и принимает последний. Поэтому, независимо от времени ввода-времени, если эти коды работают в течение последнего часа DST, это даст неправильный результат. Это означает, что это работает 99.9886% времени.

Ответ 10

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

Create Function ConvertUtcDateTimeToLocal(@utcDateTime DateTime)
Returns DateTime
Begin
    Declare @utcNow DateTime
    Declare @localNow DateTime
    Declare @timeOffSet Int

    -- Figure out the time difference between UTC and Local time
    Set @utcNow = GetUtcDate()
    Set @localNow = GetDate()
    Set @timeOffSet = DateDiff(hh, @utcNow, @localNow) 

    DECLARE @localTime datetime 

    Set @localTime = DateAdd(hh, @timeOffset, @utcDateTime) 

    -- Check Results
    return @localTime 

End
GO

Это имеет решающее значение: если часовой пояс использует дробное смещение, например, Непал, который равен GMT + 5: 45, это не сработает, потому что это касается всего целых часов. Тем не менее, он должен соответствовать вашим потребностям просто отлично.