SQL Server - 날짜 필드를 UTC로 변환
저는 최근 시스템을 현지 시간으로 저장하던 날짜/시간을 UTC로 기록하도록 업데이트했습니다.
이제 모든 로컬 저장 날짜/시간을 UTC로 변환해야 합니다.bufficious와 기능이 .NET NET »ConvertTime방법?
저는 이것을 하기 위해 유틸리티 앱을 작성해야 하는 것을 피하려고 노력하고 있습니다.
좋은 의견이라도 있나?
나는 위의 코드가 작동할 것이라고 생각하지 않습니다.그 이유는 현지 시간과 UTC 시간의 현재 날짜 차이에 따라 달라지기 때문입니다.예를 들어, 여기 캘리포니아에서는 현재 PDT(태평양 서머타임)에 있으며, 이 시간과 UTC의 차이는 7시간입니다.제공된 코드를 지금 실행하면 변환할 모든 날짜에 7시간이 추가됩니다.그러나 과거 저장된 날짜 또는 미래의 날짜를 변환하고 해당 날짜가 일광 절약 시간대가 아닌 경우에도 올바른 오프셋이 8일 때 7이 추가됩니다.결론: 현재 날짜만 보고 시간대(여름 시간을 준수하지 않는 UTC 포함) 간에 날짜/시간을 올바르게 변환할 수 없습니다.변환 중인 날짜 자체를 고려하여 일광 시간이 해당 날짜에 적용되었는지 여부를 확인해야 합니다.게다가, 낮과 표준시 자체가 바뀌는 날짜가 바뀌었습니다(조지 부시는 미국 행정부에서 날짜를 변경했습니다!).즉, getdate() 또는 getutcdate()를 참조하는 솔루션도 작동하지 않습니다.변환할 실제 날짜를 구문 분석해야 합니다.
SQL Server 2016에서는 이제 문이 포함된 표준 시간대를 기본적으로 지원합니다.다음 작업관리 변환을 연결할 수 있습니다.
SELECT YourOriginalDateTime AT TIME ZONE 'Pacific Standard Time' AT TIME ZONE 'UTC'
또는 다음과 같은 방법도 사용할 수 있습니다.
SELECT SWITCHOFFSET(YourOriginalDateTime AT TIME ZONE 'Pacific Standard Time', '+00:00')
중 시간에 UTC로 합니다.는 결는다과같다니습음과▁a▁be가 될 것입니다.datetimeoffset오프셋을 0으로 설정합니다.
모두 사용자에게 로컬인 경우 오프셋은 다음과 같습니다.
SELECT GETDATE() AS CurrentTime, GETUTCDATE() AS UTCTime
다음을 사용하여 모든 데이터를 업데이트할 수 있어야 합니다.
UPDATE SomeTable
SET DateTimeStamp = DATEADD(hh, DATEDIFF(hh, GETDATE(), GETUTCDATE()), DateTimeStamp)
그것이 효과가 있을까요, 아니면 제가 이 문제의 다른 각도를 놓치고 있나요?
앞서 언급했듯이 SQL Server에서 표준 시간대 규칙 인식 날짜 변환을 수행하는 기본 제공 방법은 없습니다(최소한 SQL Server 2012 이후).
이를 제대로 수행하려면 기본적으로 세 가지 선택 사항이 있습니다.
- SQL Server 외부에서 변환 수행 및 데이터베이스에 결과 저장
- 독립 실행형 테이블에 표준 시간대 오프셋 규칙을 도입하고 변환을 수행할 규칙 테이블을 참조할 저장 프로시저 또는 UDF를 만듭니다.SQL Server Central에서 이 접근 방식에 대한 한 가지 접근 방식을 찾을 수 있습니다(등록 필요).
- SQL CLR UDF를 생성할 수 있습니다. 여기서 접근 방식을 설명하겠습니다.
SQL Server는 표준 시간대 규칙 인식 날짜 변환을 수행하는 도구를 제공하지 않지만 .NET 프레임워크는 이러한 도구를 제공합니다. SQL CLR을 사용할 수 있는 경우 이를 활용할 수 있습니다.
Visual Studio 2012에서 데이터 도구가 설치되어 있는지 확인하고(그렇지 않으면 SQL Server 프로젝트가 옵션으로 표시되지 않음) 새 SQL Server 프로젝트를 만듭니다.
그런 다음 새 SQL CLR C# 사용자 정의 함수를 "ConvertToUtc"라고 추가합니다. VS는 다음과 같은 모양의 보일러 플레이트를 생성합니다.
public partial class UserDefinedFunctions
{
[Microsoft.SqlServer.Server.SqlFunction]
public static SqlString ConvertToUtc()
{
// Put your code here
return new SqlString (string.Empty);
}
}
우리는 여기서 몇 가지 변경을 하고 싶습니다.우선, 우리는 a를 반환하고 싶습니다.SqlDateTime보다는 니▁rather▁a.SqlString두 번째로, 우리는 유용한 것을 하고 싶습니다.:)
수정된 코드는 다음과 같습니다.
public partial class UserDefinedFunctions
{
[Microsoft.SqlServer.Server.SqlFunction]
public static SqlDateTime ConvertToUtc(SqlDateTime sqlLocalDate)
{
// convert to UTC and use explicit conversion
// to return a SqlDateTime
return TimeZone.CurrentTimeZone.ToUniversalTime(sqlLocalDate.Value);
}
}
이 시점에서, 우리는 그것을 시도할 준비가 되었습니다.가장 간단한 방법은 Visual Studio의 기본 제공 게시 기능을 사용하는 것입니다.데이터베이스 프로젝트를 마우스 오른쪽 단추로 클릭하고 "게시"를 선택합니다.데이터베이스 연결 및 이름을 설정한 다음 "게시"를 클릭하여 코드를 데이터베이스에 밀어넣거나 스크립트를 나중에 저장하거나 비트를 프로덕션에 밀어넣으려면 "스크립트 생성"을 클릭합니다.
데이터베이스에 UDF가 있으면 UDF의 작동 상태를 확인할 수 있습니다.
declare @dt as datetime
set @dt = '12/1/2013 1:00 pm'
select dbo.ConvertToUtc(@dt)
다음은 로컬 시간에서 UTC 시간으로 데이터베이스를 업그레이드한 테스트된 절차입니다.데이터베이스를 업그레이드하는 데 필요한 유일한 입력은 로컬 시간이 utctime에서 오프셋되는 시간(분)을 @Offset에 입력하고 시간대가 @ApplyDaylightSavings를 설정하여 일광 절약 시간대를 조정하는 경우에만 입력합니다.
예를 들어, 미국 중부 표준시는 6시간 동안 @Offset=-360 및 @ApplyDaylightSavings=1을 입력하고 예는 일광 절약 조정을 적용합니다.
지원 데이터베이스 기능
CREATE FUNCTION [dbo].[GetUtcDateTime](@LocalDateTime DATETIME, @Offset smallint, @ApplyDaylightSavings bit)
RETURNS DATETIME AS BEGIN
--====================================================
--Calculate the Offset Datetime
--====================================================
DECLARE @UtcDateTime AS DATETIME
SET @UtcDateTime = DATEADD(MINUTE, @Offset * -1, @LocalDateTime)
IF @ApplyDaylightSavings = 0 RETURN @UtcDateTime;
--====================================================
--Calculate the DST Offset for the UDT Datetime
--====================================================
DECLARE @Year as SMALLINT
DECLARE @DSTStartDate AS DATETIME
DECLARE @DSTEndDate AS DATETIME
--Get Year
SET @Year = YEAR(@LocalDateTime)
--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)
--Finally add the DST Offset if needed
RETURN CASE WHEN @LocalDateTime BETWEEN @DSTStartDate AND @DSTEndDate THEN
DATEADD(MINUTE, -60, @UtcDateTime)
ELSE
@UtcDateTime
END
END
GO
업그레이드 스크립트
- 이 스크립트를 실행하기 전에 백업을 만드십시오.
- @오프셋 설정 및 @일광 절약 적용
- 한 번만 뛰세요!
begin try
begin transaction;
declare @sql nvarchar(max), @Offset smallint, @ApplyDaylightSavings bit;
set @Offset = -360; --US Central Time, -300 for US Eastern Time, -480 for US West Coast
set @ApplyDaylightSavings = 1; --1 for most US time zones except Arizona which doesn't observer daylight savings, 0 for most time zones outside the US
declare rs cursor for
select 'update [' + a.TABLE_SCHEMA + '].[' + a.TABLE_NAME + '] set [' + a.COLUMN_NAME + '] = dbo.GetUtcDateTime([' + a.COLUMN_NAME + '], ' + cast(@Offset as nvarchar) + ', ' + cast(@ApplyDaylightSavings as nvarchar) + ') ;'
from INFORMATION_SCHEMA.COLUMNS a
inner join INFORMATION_SCHEMA.TABLES b on a.TABLE_SCHEMA = b.TABLE_SCHEMA and a.TABLE_NAME = b.TABLE_NAME
where a.DATA_TYPE = 'datetime' and b.TABLE_TYPE = 'BASE TABLE' ;
open rs;
fetch next from rs into @sql;
while @@FETCH_STATUS = 0 begin
exec sp_executesql @sql;
print @sql;
fetch next from rs into @sql;
end
close rs;
deallocate rs;
commit transaction;
end try
begin catch
close rs;
deallocate rs;
declare @ErrorMessage nvarchar(max), @ErrorSeverity int, @ErrorState int;
select @ErrorMessage = ERROR_MESSAGE() + ' Line ' + cast(ERROR_LINE() as nvarchar(5)), @ErrorSeverity = ERROR_SEVERITY(), @ErrorState = ERROR_STATE();
rollback transaction;
raiserror (@ErrorMessage, @ErrorSeverity, @ErrorState);
end catch
오늘이 아닌 다른 날짜를 다른 시간대로 변환해야 하는 경우 일광 절약 시간을 처리해야 합니다.저장된 기능을 사용하지 않고 데이터베이스 버전을 걱정하지 않고 Oracle로 쉽게 이식할 수 있는 솔루션을 원했습니다.
저는 워렌이 일광 절약 시간제에 대한 정확한 날짜를 얻는 것은 올바른 방향으로 가고 있다고 생각하지만, 여러 시간대와 국가의 다양한 규칙, 심지어 2006년과 2007년 사이에 미국에서 변경된 규칙에 더 유용하게 만들기 위해 위의 해결책에 대한 변형입니다.여기에는 시간대뿐만 아니라 중부 유럽도 있습니다.중부 유럽은 4월의 마지막 일요일과 10월의 마지막 일요일을 따릅니다.또한 2006년 미국은 4월의 오래된 첫 일요일, 10월의 마지막 일요일을 따른다는 것을 알게 될 것입니다.
이 SQL 코드는 보기에 약간 흉할 수 있지만, SQL Server에 복사하여 붙여넣고 시도해 보십시오.시간대 및 규칙에 대한 3가지 섹션이 있습니다.만약 당신이 1년을 더 원한다면, 그것을 년 연합에 추가하세요.다른 표준 시간대 또는 규칙에 대해서도 동일합니다.
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, z.zone, z.standard, z.daylight, z.rulename, r.strule, r.edrule,
yrs.yr + '-01-01 00:00:00' yrstart,
yrs.yr + '-12-31 23:59:59' yrend,
yrs.yr + r.stdtpart + ' ' + r.cngtime stmonthref,
yrs.yr + r.eddtpart + ' ' + r.cngtime edmonthref,
case when r.strule in ('1', '2', '3') then case when datepart(dw, yrs.yr + r.stdtpart) = '1' then 0 else 8 - datepart(dw, yrs.yr + r.stdtpart) end
else (datepart(dw, yrs.yr + r.stdtpart) - 1) * -1 end stdowref,
case when r.edrule in ('1', '2', '3') then case when datepart(dw, yrs.yr + r.eddtpart) = '1' then 0 else 8 - datepart(dw, yrs.yr + r.eddtpart) end
else (datepart(dw, yrs.yr + r.eddtpart) - 1) * -1 end eddowref,
datename(dw, yrs.yr + r.stdtpart) stdow,
datename(dw, yrs.yr + r.eddtpart) eddow,
case when r.strule in ('1', '2', '3') then (7 * CAST(r.strule AS Integer)) - 7 else 0 end stweekadd,
case when r.edrule in ('1', '2', '3') then (7 * CAST(r.edrule AS Integer)) - 7 else 0 end edweekadd
from (
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 '2019' 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
) yrs
cross join (
SELECT 'ET' zone, '-05:00' standard, '-04:00' daylight, 'US' rulename
UNION SELECT 'CT' zone, '-06:00' standard, '-05:00' daylight, 'US' rulename
UNION SELECT 'MT' zone, '-07:00' standard, '-06:00' daylight, 'US' rulename
UNION SELECT 'PT' zone, '-08:00' standard, '-07:00' daylight, 'US' rulename
UNION SELECT 'CET' zone, '+01:00' standard, '+02:00' daylight, 'EU' rulename
) z
join (
SELECT 'US' rulename, '2' strule, '-03-01' stdtpart, '1' edrule, '-11-01' eddtpart, 2007 firstyr, 2099 lastyr, '02:00:00' cngtime
UNION SELECT 'US' rulename, '1' strule, '-04-01' stdtpart, 'L' edrule, '-10-31' eddtpart, 1900 firstyr, 2006 lastyr, '02:00:00' cngtime
UNION SELECT 'EU' rulename, 'L' strule, '-03-31' stdtpart, 'L' edrule, '-10-31' eddtpart, 1900 firstyr, 2099 lastyr, '01:00:00' cngtime
) r on r.rulename = z.rulename
and datepart(year, yrs.yr) between firstyr and lastyr
) dstdates
규칙은 첫째, 둘째, 셋째 또는 마지막 일요일에 1, 2, 3 또는 L을 사용합니다.날짜 부분은 규칙에 따라 규칙 유형 L에 대해 월의 첫 번째 날 또는 월의 마지막 날을 제공합니다.
위의 질문을 뷰에 넣었습니다.이제 시간대 오프셋이 있거나 UTC 시간으로 변환된 날짜를 원할 때마다 이 보기에 참여하여 날짜 형식으로 날짜 가져오기를 선택합니다.날짜 시간 대신 날짜 시간 오프셋으로 변환했습니다.
select createdon, dst.zone
, case when createdon >= dstlow and createdon < dsthigh then dst.daylight else dst.standard end pacificoffsettime
, TODATETIMEOFFSET(createdon, case when createdon >= dstlow and createdon < dsthigh then dst.daylight else dst.standard end) pacifictime
, SWITCHOFFSET(TODATETIMEOFFSET(createdon, case when createdon >= dstlow and createdon < dsthigh then dst.daylight else dst.standard end), '+00:00') utctime
from (select '2014-01-01 12:00:00' createdon union select '2014-06-01 12:00:00' createdon) photos
left join US_DAYLIGHT_DATES dst on createdon between yrstart and yrend and zone = 'PT'
여기 제 빠르고 더러운 버전이 있습니다.저는 제 모든 데이트 상대가 미국 동부 표준 시간대를 사용했다는 것을 알고 있습니다.오프셋을 변경하거나 필요에 따라 스마트하게 만들 수 있습니다.나는 한 번의 이주를 하고 있었기 때문에 이것은 충분했습니다.
CREATE FUNCTION [dbo].[ConvertToUtc]
(
@date datetime
)
RETURNS DATETIME
AS
BEGIN
-- Declare the return variable here
DECLARE @utcDate datetime;
DECLARE @offset int;
SET @offset = (SELECT CASE WHEN
@date BETWEEN '1987-04-05 02:00 AM' AND '1987-10-25 02:00 AM'
OR @date BETWEEN '1988-04-03 02:00 AM' AND '1988-10-30 02:00 AM'
OR @date BETWEEN '1989-04-02 02:00 AM' AND '1989-10-29 02:00 AM'
OR @date BETWEEN '1990-04-01 02:00 AM' AND '1990-10-28 02:00 AM'
OR @date BETWEEN '1991-04-07 02:00 AM' AND '1991-10-27 02:00 AM'
OR @date BETWEEN '1992-04-05 02:00 AM' AND '1992-10-25 02:00 AM'
OR @date BETWEEN '1993-04-04 02:00 AM' AND '1993-10-31 02:00 AM'
OR @date BETWEEN '1994-04-03 02:00 AM' AND '1994-10-30 02:00 AM'
OR @date BETWEEN '1995-04-02 02:00 AM' AND '1995-10-29 02:00 AM'
OR @date BETWEEN '1996-04-07 02:00 AM' AND '1996-10-27 02:00 AM'
OR @date BETWEEN '1997-04-06 02:00 AM' AND '1997-10-26 02:00 AM'
OR @date BETWEEN '1998-04-05 02:00 AM' AND '1998-10-25 02:00 AM'
OR @date BETWEEN '1999-04-04 02:00 AM' AND '1999-10-31 02:00 AM'
OR @date BETWEEN '2000-04-02 02:00 AM' AND '2000-10-29 02:00 AM'
OR @date BETWEEN '2001-04-01 02:00 AM' AND '2001-10-28 02:00 AM'
OR @date BETWEEN '2002-04-07 02:00 AM' AND '2002-10-27 02:00 AM'
OR @date BETWEEN '2003-04-06 02:00 AM' AND '2003-10-26 02:00 AM'
OR @date BETWEEN '2004-04-04 02:00 AM' AND '2004-10-31 02:00 AM'
OR @date BETWEEN '2005-04-03 02:00 AM' AND '2005-10-30 02:00 AM'
OR @date BETWEEN '2006-04-02 02:00 AM' AND '2006-10-29 02:00 AM'
OR @date BETWEEN '2007-03-11 02:00 AM' AND '2007-11-04 02:00 AM'
OR @date BETWEEN '2008-03-09 02:00 AM' AND '2008-11-02 02:00 AM'
OR @date BETWEEN '2009-03-08 02:00 AM' AND '2009-11-01 02:00 AM'
OR @date BETWEEN '2010-03-14 02:00 AM' AND '2010-11-07 02:00 AM'
OR @date BETWEEN '2011-03-13 02:00 AM' AND '2011-11-06 02:00 AM'
OR @date BETWEEN '2012-03-11 02:00 AM' AND '2012-11-04 02:00 AM'
OR @date BETWEEN '2013-03-10 02:00 AM' AND '2013-11-03 02:00 AM'
OR @date BETWEEN '2014-03-09 02:00 AM' AND '2014-11-02 02:00 AM'
OR @date BETWEEN '2015-03-08 02:00 AM' AND '2015-11-01 02:00 AM'
OR @date BETWEEN '2016-03-13 02:00 AM' AND '2016-11-06 02:00 AM'
OR @date BETWEEN '2017-03-12 02:00 AM' AND '2017-11-05 02:00 AM'
OR @date BETWEEN '2018-03-11 02:00 AM' AND '2018-11-04 02:00 AM'
OR @date BETWEEN '2019-03-10 02:00 AM' AND '2019-11-03 02:00 AM'
OR @date BETWEEN '2020-03-08 02:00 AM' AND '2020-11-01 02:00 AM'
OR @date BETWEEN '2021-03-14 02:00 AM' AND '2021-11-07 02:00 AM'
THEN 4
ELSE 5 END);
SELECT @utcDate = DATEADD(hh, @offset, @date)
RETURN @utcDate;
END
위의 (가능성이 있는) 것을 놓친 경우를 제외하고, 위의 모든 방법은 일광 절약 시간(EDT)에서 표준 시간(EST)으로 전환할 때 중복을 고려하지 않는다는 점에서 결함이 있습니다.(매우 장황한) 예:
[1] EDT 2016-11-06 00:59 - UTC 2016-11-06 04:59
[2] EDT 2016-11-06 01:00 - UTC 2016-11-06 05:00
[3] EDT 2016-11-06 01:30 - UTC 2016-11-06 05:30
[4] EDT 2016-11-06 01:59 - UTC 2016-11-06 05:59
[5] EST 2016-11-06 01:00 - UTC 2016-11-06 06:00
[6] EST 2016-11-06 01:30 - UTC 2016-11-06 06:30
[7] EST 2016-11-06 01:59 - UTC 2016-11-06 06:59
[8] EST 2016-11-06 02:00 - UTC 2016-11-06 07:00
날짜와 시간을 기반으로 한 단순한 시간 오프셋으로는 시간이 단축되지 않습니다.01:00~01:59 사이에 EDT 또는 EST에 현지 시간이 기록되었는지 모른다면, 여러분은 단서를 잡을 수 없을 것입니다.예를 들어 01:30을 사용해 보겠습니다. 그 전 01:31에서 01:59 사이의 나중 시간을 찾으면 01:30이 [3]인지 [6]인지 알 수 없습니다.이 경우, 이전 항목을 보고 약간의 코딩으로 정확한 UTC 시간을 얻을 수 있습니다(SQL에서는 재미가 없습니다). 이것이 BEST 사례입니다...
다음 현지 시간을 기록했으며 EDT 또는 EST를 표시하기 위해 한 푼도 할애하지 않았다고 가정해 보십시오.
UTC time UTC time UTC time
if [2] and [3] if [2] and [3] if [2] before
local time before switch after switch and [3] after
[1] 2016-11-06 00:43 04:43 04:43 04:43
[2] 2016-11-06 01:15 05:15 06:15 05:15
[3] 2016-11-06 01:45 05:45 06:45 06:45
[4] 2016-11-06 03:25 07:25 07:25 07:25
시간 [2] 및 [3]은(는) 오전 5시, 오전 6시 또는 오전 5시 및 오전 6시 중 하나일 수 있습니다. 즉, 호스로 연결되어 있으며 01:00:00에서 01:59:59 사이의 모든 판독값을 버려야 합니다.이러한 상황에서는 실제 UTC 시간을 해결할 방법이 전혀 없습니다!
다음은 실행 중인 서버의 DATE와 UTCDATE 간의 차이를 계산하고 해당 오프셋을 사용하여 사용자가 서버에 전달한 날짜와 동일한 UTC를 계산할 때 작동합니다.제 예에서는 UTC 오프셋이 -630분인 호주 애들레이드에서 '1-nov-2012 06:00'에 대한 UTC 등가를 변환하려고 합니다. 이 경우 날짜를 추가하면 로컬 날짜와 동일한 UTC가 됩니다.
DATEADD(MINET, DATEIFF(MINET, GETDATE(), GETUTCDATE(), '1-nov-2012 06:00')를 선택합니다.
시간을 얼마나 거슬러 올라가야 하는지에 따라 일광 절약 시간 표를 작성한 다음 표에 참여하여 주의 깊게 변환할 수 있습니다.이는 EST에서 GMT로 변환합니다(즉, 오프셋 5 및 4 사용).
select createdon, dateadd(hour, case when dstlow is null then 5 else 4 end, createdon) as gmt
from photos
left outer join (
SELECT {ts '2009-03-08 02:00:00'} as dstlow, {ts '2009-11-01 02:00:00'} as dsthigh
UNION ALL SELECT {ts '2010-03-14 02:00:00'} as dstlow, {ts '2010-11-07 02:00:00'} as dsthigh
UNION ALL SELECT {ts '2011-03-13 02:00:00'} as dstlow, {ts '2011-11-06 02:00:00'} as dsthigh
UNION ALL SELECT {ts '2012-03-11 02:00:00'} as dstlow, {ts '2012-11-04 02:00:00'} as dsthigh
UNION ALL SELECT {ts '2013-03-10 02:00:00'} as dstlow, {ts '2013-11-03 02:00:00'} as dsthigh
UNION ALL SELECT {ts '2014-03-09 02:00:00'} as dstlow, {ts '2014-11-02 02:00:00'} as dsthigh
UNION ALL SELECT {ts '2015-03-08 02:00:00'} as dstlow, {ts '2015-11-01 02:00:00'} as dsthigh
UNION ALL SELECT {ts '2016-03-13 02:00:00'} as dstlow, {ts '2016-11-06 02:00:00'} as dsthigh
UNION ALL SELECT {ts '2017-03-12 02:00:00'} as dstlow, {ts '2017-11-05 02:00:00'} as dsthigh
UNION ALL SELECT {ts '2018-03-11 02:00:00'} as dstlow, {ts '2018-11-04 02:00:00'} as dsthigh
) dst
on createdon >= dstlow and createdon < dsthigh
우리는 ServerZone ServerZone을 할 수 .DateTime및 ServerZone UTC(UTC에서 서버존으로)DateTime
다음 스크립트를 실행하여 변환을 이해하고 필요에 따라 수정합니다.
--Get Server's TimeZone
DECLARE @ServerTimeZone VARCHAR(50)
EXEC MASTER.dbo.xp_regread 'HKEY_LOCAL_MACHINE',
'SYSTEM\CurrentControlSet\Control\TimeZoneInformation',
'TimeZoneKeyName',@ServerTimeZone OUT
-- ServerZone to UTC DATETIME
DECLARE @CurrentServerZoneDateTime DATETIME = GETDATE()
DECLARE @UTCDateTime DATETIME = @CurrentServerZoneDateTime AT TIME ZONE @ServerTimeZone AT TIME ZONE 'UTC'
--(OR)
--DECLARE @UTCDateTime DATETIME = GETUTCDATE()
SELECT @CurrentServerZoneDateTime AS CURRENTZONEDATE,@UTCDateTime AS UTCDATE
-- UTC to ServerZone DATETIME
SET @CurrentServerZoneDateTime = @UTCDateTime AT TIME ZONE 'UTC' AT TIME ZONE @ServerTimeZone
SELECT @UTCDateTime AS UTCDATE,@CurrentServerZoneDateTime AS CURRENTZONEDATE
참고: 이(AT TIME ZONE) SQL Server 2016+에서만 작업하며 이 장점은 특정 시간대로 변환하는 동안 자동으로 일광 절약 시간대를 고려합니다.
게임에 조금 늦었지만 SQL 2012에서 이와 같은 작업을 수행해야 했습니다. 아직 완전히 테스트하지는 않았지만 다음과 같이 생각해냈습니다.
CREATE FUNCTION SMS.fnConvertUTC
(
@DateCST datetime
)
RETURNS DATETIME
AS
BEGIN
RETURN
CASE
WHEN @DateCST
BETWEEN
CASE WHEN @DateCST > '2007-01-01'
THEN CONVERT(DATETIME, CONVERT(VARCHAR,YEAR(@DateCST)) + '-MAR-14 02:00') - DATEPART(DW,CONVERT(VARCHAR,YEAR(@DateCST)) + '-MAR-14 02:00' ) + 1
ELSE CONVERT(DATETIME, CONVERT(VARCHAR,YEAR(@DateCST)) + '-APR-07 02:00') - DATEPART(DW,CONVERT(VARCHAR,YEAR(@DateCST)) + '-APR-07 02:00' ) + 1 END
AND
CASE WHEN @DateCST > '2007-01-01'
THEN CONVERT(DATETIME, CONVERT(VARCHAR,YEAR(@DateCST)) + '-NOV-07 02:00') - DATEPART(DW,CONVERT(VARCHAR,YEAR(@DateCST)) + '-NOV-07 02:00' ) + 1
ELSE CONVERT(DATETIME, CONVERT(VARCHAR,YEAR(@DateCST)) + '-OCT-31 02:00') - DATEPART(DW,CONVERT(VARCHAR,YEAR(@DateCST)) + '-OCT-31 02:00' ) + 1 END
THEN DATEADD(HOUR,4,@DateCST)
ELSE DATEADD(HOUR,5,@DateCST)
END
END
위에 누군가 정적 목록 DST 날짜를 게시했기 때문에 아래 쿼리를 작성하여 이 코드의 출력을 해당 목록과 비교했습니다.지금까지 그것은 정확해 보입니다.
;WITH DT AS
(
SELECT MyDate = GETDATE()
UNION ALL
SELECT MyDate = DATEADD(YEAR,-1,MyDate) FROM DT
WHERE DATEADD(YEAR,-1,MyDate) > DATEADD(YEAR, -30, GETDATE())
)
SELECT
SpringForward = CASE
WHEN MyDate > '2007-01-01'
THEN CONVERT(DATETIME, CONVERT(VARCHAR,YEAR(MyDate)) + '-MAR-14 02:00') - DATEPART(DW,CONVERT(VARCHAR,YEAR(MyDate)) + '-MAR-14 02:00' ) + 1
ELSE CONVERT(DATETIME, CONVERT(VARCHAR,YEAR(MyDate)) + '-APR-07 02:00') - DATEPART(DW,CONVERT(VARCHAR,YEAR(MyDate)) + '-APR-07 02:00' ) + 1 END
, FallBackward = CASE
WHEN MyDate > '2007-01-01'
THEN CONVERT(DATETIME, CONVERT(VARCHAR,YEAR(MyDate)) + '-NOV-07 02:00') - DATEPART(DW,CONVERT(VARCHAR,YEAR(MyDate)) + '-NOV-07 02:00' ) + 1
ELSE CONVERT(DATETIME, CONVERT(VARCHAR,YEAR(MyDate)) + '-OCT-31 02:00') - DATEPART(DW,CONVERT(VARCHAR,YEAR(MyDate)) + '-OCT-31 02:00' ) + 1 END
FROM DT
ORDER BY 1 DESC
SpringForward FallBackward
---------------- ----------------
2020-03-08 02:00 2020-11-01 02:00
2019-03-10 02:00 2019-11-03 02:00
2018-03-11 02:00 2018-11-04 02:00
2017-03-12 02:00 2017-11-05 02:00
2016-03-13 02:00 2016-11-06 02:00
2015-03-08 02:00 2015-11-01 02:00
2014-03-09 02:00 2014-11-02 02:00
2013-03-10 02:00 2013-11-03 02:00
2012-03-11 02:00 2012-11-04 02:00
2011-03-13 02:00 2011-11-06 02:00
2010-03-14 02:00 2010-11-07 02:00
2009-03-08 02:00 2009-11-01 02:00
2008-03-09 02:00 2008-11-02 02:00
2007-03-11 02:00 2007-11-04 02:00
2006-04-02 02:00 2006-10-29 02:00
2005-04-03 02:00 2005-10-30 02:00
2004-04-04 02:00 2004-10-31 02:00
2003-04-06 02:00 2003-10-26 02:00
2002-04-07 02:00 2002-10-27 02:00
2001-04-01 02:00 2001-10-28 02:00
2000-04-02 02:00 2000-10-29 02:00
1999-04-04 02:00 1999-10-31 02:00
1998-04-05 02:00 1998-10-25 02:00
1997-04-06 02:00 1997-10-26 02:00
1996-04-07 02:00 1996-10-27 02:00
1995-04-02 02:00 1995-10-29 02:00
1994-04-03 02:00 1994-10-30 02:00
1993-04-04 02:00 1993-10-31 02:00
1992-04-05 02:00 1992-10-25 02:00
1991-04-07 02:00 1991-10-27 02:00
(30 row(s) affected)
언급URL : https://stackoverflow.com/questions/2700197/sql-server-convert-date-field-to-utc
'programing' 카테고리의 다른 글
| 아이폰 UIView 애니메이션 모범 사례 (0) | 2023.06.20 |
|---|---|
| 다양한 필드를 가진 장고 모델의 객체를 만드는 방법은 무엇입니까? (0) | 2023.06.20 |
| Oracle 중단 프로세스 문제 해결 (0) | 2023.06.20 |
| MongoDB $pull을 사용하여 배열 내의 문서 삭제 (0) | 2023.06.20 |
| 현재 사용자 언어 찾기 (0) | 2023.06.20 |