Как создать летнее время Функция запуска и завершения в SQL Server
Мне нужно создать функцию на SQL-сервере, которая возвращает летнее время, начинающееся с даты и времени и с летнего времени, заканчивающегося datetime.
Я нашел несколько примеров в Интернете, однако все они используют 1-ю дату марта и 1-ю дату ноября, и это не является технически корректным.
Переход на летнее время начинается в 2 часа ночи во второе воскресенье марта и заканчивается в 2 часа ночи в первое воскресенье ноября.
Я начал с кода ниже, но я уверен, что это неправильно. Любая помощь приветствуется!:)
DECLARE @DSTSTART DATETIME
SELECT @DSTSTART = CASE WHEN
DATEPART(MONTH, SYSDATETIME()) = 3
AND DATEPART(weekday, SYSDATETIME()) = 1
AND DATEDIFF(week,dateadd(week, datediff(week, 0, dateadd(month, datediff(month, 0, SYSDATETIME()), 0)), 0), SYSDATETIME() - 1) = 2
AND DATEPART(HOUR, SYSDATETIME()) = 2
THEN SYSDATETIME()
END
RETURN (@DSTSTART)
END
GO
Ответы
Ответ 1
Не забывайте, что расписания летнего времени меняются в зависимости от страны, а также могут меняться с годами: например, в 2007 году применялась действующая система США.
Предполагая, что вам нужна текущая система для США, здесь одна форма ответа за любой данный год.
SET DATEFIRST 7
DECLARE @year INT = 2013
DECLARE
@StartOfMarch DATETIME ,
@StartOfNovember DATETIME ,
@DstStart DATETIME ,
@DstEnd DATETIME
SET @StartOfMarch = DATEADD(MONTH, 2, DATEADD(YEAR, @year - 1900, 0))
SET @StartOfNovember = DATEADD(MONTH, 10, DATEADD(YEAR, @year - 1900, 0));
SET @DstStart = DATEADD(HOUR, 2,
DATEADD(day,
( ( 15 - DATEPART(dw, @StartOfMarch) ) % 7 )
+ 7, @StartOfMarch))
SET @DstEnd = DATEADD(HOUR, 2,
DATEADD(day,
( ( 8 - DATEPART(dw, @StartOfNovember) ) % 7 ),
@StartOfNovember))
SELECT
@DstStart AS DstStartInUS ,
@DstEnd AS DstEndInUS
или как функции, но вы должны знать, что DateFirst имеет значение 7, иначе математика будет отключена.
CREATE FUNCTION GetDstStart ( @Year AS INT )
RETURNS DATETIME
AS
BEGIN
DECLARE
@StartOfMarch DATETIME ,
@DstStart DATETIME
SET @StartOfMarch = DATEADD(MONTH, 2,
DATEADD(YEAR, @year - 1900, 0))
SET @DstStart = DATEADD(HOUR, 2,
DATEADD(day,
( ( 15 - DATEPART(dw,
@StartOfMarch) )
% 7 ) + 7, @StartOfMarch))
RETURN @DstStart
END
GO;
CREATE FUNCTION GetDstEnd ( @Year AS INT )
RETURNS DATETIME
AS
BEGIN
DECLARE
@StartOfNovember DATETIME ,
@DstEnd DATETIME
SET @StartOfNovember = DATEADD(MONTH, 10,
DATEADD(YEAR, @year - 1900, 0))
SET @DstEnd = DATEADD(HOUR, 2,
DATEADD(day,
( ( 8 - DATEPART(dw,
@StartOfNovember) )
% 7 ), @StartOfNovember))
RETURN @DstEnd
END
Ответ 2
Лично я считаю, что легче найти первое воскресенье ноября, чем найти второе воскресенье марта. К счастью, если вы найдете его, вы можете найти другое, потому что между ними всегда 238 дней. Итак, здесь удобная функция для поиска конца Dst:
create function GetDstEnd (
@Year int
)
returns datetime
as
begin
declare @DstEnd datetime;
;with FirstWeekOfNovember
as (
select top(7)
cast(@Year as char(4))
+ '-11-0'
+ cast(row_number() over(order by object_id) as char(1))
+ ' 02:00:00'
'DST_Stops'
from sys.columns
)
select @DstEnd = DST_Stops
from FirstWeekOfNovember
where datepart(weekday,DST_Stops) = 1
return @DstEnd;
end;
Теперь начало Dst - это одна и та же функция, только на 238 дней раньше.
create function GetDstStart (
@Year int
)
returns datetime
as
begin;
declare @DstStart datetime;
;with FirstWeekOfNovember
as (
select top(7)
cast(@Year as char(4))
+ '-11-0'
+ cast(row_number() over(order by object_id) as char(1))
+ ' 02:00:00'
'DST_Stops'
from sys.columns
)
select @DstStart = dateadd(day,-238,DST_Stops)
from FirstWeekOfNovember
where datepart(weekday,DST_Stops) = 1
return @DstStart;
end;
go
Ответ 3
SQL Server версии 2016 решит эту проблему раз и навсегда. Для более ранних версий решение CLR, вероятно, проще всего. Или для определенного правила DST (например, только для США) функция T-SQL может быть относительно простой.
Однако, я думаю, что общее решение T-SQL может быть возможным. Пока xp_regread работает, попробуйте следующее:
CREATE TABLE #tztable (Value varchar(50), Data binary(56));
DECLARE @tzname varchar(150) = 'SYSTEM\CurrentControlSet\Control\TimeZoneInformation'
EXEC master.dbo.xp_regread 'HKEY_LOCAL_MACHINE', @tzname, 'TimeZoneKeyName', @tzname OUT;
SELECT @tzname = 'SOFTWARE\Microsoft\Windows NT\CurrentVersion\Time Zones\' + @tzname
INSERT INTO #tztable
EXEC master.dbo.xp_regread 'HKEY_LOCAL_MACHINE', @tzname, 'TZI';
SELECT -- See http://msdn.microsoft.com/ms725481
CAST(CAST(REVERSE(SUBSTRING(Data, 1, 4)) AS binary(4)) AS int) AS BiasMinutes, -- UTC = local + bias: > 0 in US, < 0 in Europe!
CAST(CAST(REVERSE(SUBSTRING(Data, 5, 4)) AS binary(4)) AS int) AS ExtraBias_Std, -- 0 for most timezones
CAST(CAST(REVERSE(SUBSTRING(Data, 9, 4)) AS binary(4)) AS int) AS ExtraBias_DST, -- -60 for most timezones: DST makes UTC 1 hour earlier
-- When DST ends:
CAST(CAST(REVERSE(SUBSTRING(Data, 13, 2)) AS binary(2)) AS smallint) AS StdYear, -- 0 = yearly (else once)
CAST(CAST(REVERSE(SUBSTRING(Data, 15, 2)) AS binary(2)) AS smallint) AS StdMonth, -- 0 = no DST
CAST(CAST(REVERSE(SUBSTRING(Data, 17, 2)) AS binary(2)) AS smallint) AS StdDayOfWeek, -- 0 = Sunday to 6 = Saturday
CAST(CAST(REVERSE(SUBSTRING(Data, 19, 2)) AS binary(2)) AS smallint) AS StdWeek, -- 1 to 4, or 5 = last <DayOfWeek> of <Month>
CAST(CAST(REVERSE(SUBSTRING(Data, 21, 2)) AS binary(2)) AS smallint) AS StdHour, -- Local time
CAST(CAST(REVERSE(SUBSTRING(Data, 23, 2)) AS binary(2)) AS smallint) AS StdMinute,
CAST(CAST(REVERSE(SUBSTRING(Data, 25, 2)) AS binary(2)) AS smallint) AS StdSecond,
CAST(CAST(REVERSE(SUBSTRING(Data, 27, 2)) AS binary(2)) AS smallint) AS StdMillisec,
-- When DST starts:
CAST(CAST(REVERSE(SUBSTRING(Data, 29, 2)) AS binary(2)) AS smallint) AS DSTYear, -- See above
CAST(CAST(REVERSE(SUBSTRING(Data, 31, 2)) AS binary(2)) AS smallint) AS DSTMonth,
CAST(CAST(REVERSE(SUBSTRING(Data, 33, 2)) AS binary(2)) AS smallint) AS DSTDayOfWeek,
CAST(CAST(REVERSE(SUBSTRING(Data, 35, 2)) AS binary(2)) AS smallint) AS DSTWeek,
CAST(CAST(REVERSE(SUBSTRING(Data, 37, 2)) AS binary(2)) AS smallint) AS DSTHour,
CAST(CAST(REVERSE(SUBSTRING(Data, 39, 2)) AS binary(2)) AS smallint) AS DSTMinute,
CAST(CAST(REVERSE(SUBSTRING(Data, 41, 2)) AS binary(2)) AS smallint) AS DSTSecond,
CAST(CAST(REVERSE(SUBSTRING(Data, 43, 2)) AS binary(2)) AS smallint) AS DSTMillisec
FROM #tztable;
DROP TABLE #tztable
A (сложная) функция T-SQL может использовать эти данные, чтобы определить точное смещение для всех дат в течение текущего правила DST.
