prosource

SQL Server에서 bigint(UNIX 타임스탬프)를 datetime으로 변환하려면 어떻게 해야 합니까?

probook 2023. 4. 13. 20:56
반응형

SQL Server에서 bigint(UNIX 타임스탬프)를 datetime으로 변환하려면 어떻게 해야 합니까?

SQL Server에서 UNIX 타임스탬프(bigint)를 Date Time으로 변환하려면 어떻게 해야 합니까?

이 방법은 효과가 있었습니다.

Select
    dateadd(S, [unixtime], '1970-01-01')
From [Table]

1970-01-01년에 왜 그랬는지 궁금해 하는 사람이 있다면, 이것은 에폭 타임이라고 불린다.

다음은 Wikipedia에서 인용한 내용입니다.

1970년 1월 1일 목요일 00:00:00:00:00 Coordinated Universal Time(UTC; 세계 표준시)[1][주 1]이후 경과한 초수. 윤초는 카운트되지 않습니다.

2038년의 문제

를 취득합니다.int이치노 더 2147483647초이 문제를 해결하려면 DateAdd에 대한 추가 콜을 몇 년 동안1 콜과 나머지 몇 초 동안1 콜로 나눌 수 있습니다.

Declare @t as bigint = 4147483645

Select (@t / @oneyear) -- Years to add
Select (@t % @oneyear) -- Remaining seconds to add

-- Get Date given a timestamp @t
Declare @oneyear as int = 31622400
Select DateAdd(SECOND, @t % @oneyear, DateAdd(YEAR, @t / @oneyear, '1970-01-01'))

이를 통해 2038보다 큰 년을 나타내는 타임스탬프를 변환할 수 있습니다.

시험:

CREATE FUNCTION dbo.fn_ConvertToDateTime (@Datetime BIGINT)
RETURNS DATETIME
AS
BEGIN
    DECLARE @LocalTimeOffset BIGINT
           ,@AdjustedLocalDatetime BIGINT;
    SET @LocalTimeOffset = DATEDIFF(second,GETDATE(),GETUTCDATE())
    SET @AdjustedLocalDatetime = @Datetime - @LocalTimeOffset
    RETURN (SELECT DATEADD(second,@AdjustedLocalDatetime, CAST('1970-01-01 00:00:00' AS datetime)))
END;
GO

아래 오류가 발생하는 경우:

식을 데이터 형식 int로 변환하는 동안 산술 오버플로 오류가 발생했습니다.

unix 타임스탬프가 bigint(int)로 되어 있기 때문에, 다음과 같이 사용할 수 있습니다.

SELECT DATEADD(S, CONVERT(int,LEFT(1462924862735870900, 10)), '1970-01-01')
FROM TABLE

실제 컬럼의 하드코드 타임스탬프를 unix-timestamp로 바꿉니다.

출처: MSSQL bigint Unix Timestamp to Datetime (밀리초 단위)

이것처럼.

Unix(Epoch) 날짜/시간을 기준 날짜에 초 단위로 추가

이것으로 현시점에서는 취득할 수 있습니다(2010-05-25 07:56:23.000).

 SELECT dateadd(s,1274756183,'19700101 05:00:00:000')

되돌리려면 http://wiki.lessthandot.com/index.php/Epoch_Date를 참조하십시오.

테스트:

SQL 서버:

SELECT dateadd(S, timestamp, '1970-01-01 00:00:00') 
     FROM 
your_table

MySql 서버:

SELECT
  from_unixtime(timestamp) 
FROM 
  your_table

http://www.w3resource.com/mysql/date-and-time-functions/mysql-from_unixtime-function.php

이것으로 끝입니다.

declare @UNIX_TIME int
select @UNIX_TIME = 1111111111
-- Using dateadd to add seconds to 1970-01-01
select [Datetime from UNIX Time] = dateadd(!precision!,@UNIX_TIME,'1970-01-01')

정확도 대신!타임스탬프의 정밀도에 따라 ss, ms 또는 mcs를 사용합니다.Bigint는 마이크로초의 정밀도를 유지할 수 있습니다.

시간이 밀리초 단위이고 이를 보존해야 하는 경우:

DECLARE @value VARCHAR(32) = '1561487667713';

SELECT DATEADD(MILLISECOND, CAST(RIGHT(@value, 3) AS INT) - DATEDIFF(MILLISECOND,GETDATE(),GETUTCDATE()), DATEADD(SECOND, CAST(LEFT(@value, 10) AS INT), '1970-01-01T00:00:00'))

n초 추가1970-01-01는 Unix 타임스탬프인n을 1970년 1월1일 목요일 00:00:00:00 Coordinated Universal Time(UTC; 세계 표준시) 이후 경과한 초수이기 때문에 UTC 날짜를 지정합니다.

SQL Server 2016에서는 를 사용하여 표준 시간대를 다른 표준 시간대로 변환할 수 있습니다.Windows 표준 형식의 표준 시간대 이름만 알면 됩니다.

SELECT *
FROM (VALUES (1514808000), (1527854400)) AS Tests(UnixTimestamp)
CROSS APPLY (SELECT DATEADD(SECOND, UnixTimestamp, '1970-01-01') AT TIME ZONE 'UTC') AS CA1(UTCDate)
CROSS APPLY (SELECT UTCDate AT TIME ZONE 'Pacific Standard Time') AS CA2(LocalDate)

| UnixTimestamp | UTCDate                    | LocalDate                  |
|---------------|----------------------------|----------------------------|
| 1514808000    | 2018-01-01 12:00:00 +00:00 | 2018-01-01 04:00:00 -08:00 |
| 1527854400    | 2018-06-01 12:00:00 +00:00 | 2018-06-01 05:00:00 -07:00 |

또는 간단하게:

SELECT *, DATEADD(SECOND, UnixTimestamp, '1970-01-01') AT TIME ZONE 'UTC' AT TIME ZONE 'Pacific Standard Time'
FROM (VALUES (1514808000), (1527854400)) AS Tests(UnixTimestamp)

| UnixTimestamp | LocalDate                  |
|---------------|----------------------------|
| 1514808000    | 2018-01-01 04:00:00 -08:00 |
| 1527854400    | 2018-06-01 05:00:00 -07:00 |

주의:

  • 타임존 정보를 캐스팅하여 잘라낼 수 있습니다.DATETIMEOFFSET로.DATETIME.
  • 변환에는 서머타임이 고려됩니다.태평양 표준시는 2018년 1월 UTC-08:00, 2018년 6월 UTC-07:00이다.

@Daniel Little은 구체적인 질문에 가장 쉽고 우아한 답변을 가지고 있다.다만, 특정의 타임 존으로 변환해, DST(Daylight Savings Time)를 고려하는 경우는, 다음의 조작이 유효합니다.

CAST(DATEADD(S, [UnixTimestamp], '1970-01-01') AT TIME ZONE 'UTC' AT TIME ZONE 'Pacific Standard Time' AS Datetime)

주의: 이 솔루션은 SQL Server 2016 이상(및 Azure)에서만 작동합니다.

함수를 작성하려면:

CREATE FUNCTION dbo.ConvertUnixTime (@input INT)
RETURNS Datetime
AS BEGIN
    DECLARE @Unix Datetime

    SET @Unix = CAST(DATEADD(S, @Input, '1970-01-01') AT TIME ZONE 'UTC' AT TIME ZONE 'Pacific Standard Time' AS Datetime)

    RETURN @Unix
END

다음과 같이 함수를 호출할 수 있습니다.

SELECT   dbo.ConvertUnixTime([UnixTimestamp])
FROM     YourTable

이는 Daniel Little이 이 질문에 대해 수행한 작업을 기반으로 하지만 여름 시간(날짜 추가 함수에 대한 제한으로 인해 1902년 01 ~ 01년 이후 날짜)을 고려한 것입니다.

먼저 여름 시간의 날짜 범위를 저장하는 테이블을 만들어야 합니다(출처:미국의 역사):

CREATE TABLE [dbo].[CFG_DAY_LIGHT_SAVINGS_TIME](
  [BEGIN_DATE] [datetime] NULL,
  [END_DATE] [datetime] NULL,
  [YEAR_DATE] [smallint] NULL
) ON [PRIMARY]

GO

INSERT INTO CFG_DAY_LIGHT_SAVINGS_TIME VALUES
('2001-04-01 02:00:00.000',   '2001-10-27 01:59:59.997',    2001),
('2002-04-07 02:00:00.000',   '2002-10-26 01:59:59.997',    2002),
('2003-04-06 02:00:00.000',   '2003-10-25 01:59:59.997',    2003),
('2004-04-04 02:00:00.000',   '2004-10-30 01:59:59.997',    2004),
('2005-04-03 02:00:00.000',   '2005-10-29 01:59:59.997',    2005),
('2006-04-02 02:00:00.000',   '2006-10-28 01:59:59.997',    2006),
('2007-03-11 02:00:00.000',   '2007-11-03 01:59:59.997',    2007),
('2008-03-09 02:00:00.000',   '2008-11-01 01:59:59.997',    2008),
('2009-03-08 02:00:00.000',   '2009-10-31 01:59:59.997',    2009),
('2010-03-14 02:00:00.000',   '2010-11-06 01:59:59.997',    2010),
('2011-03-13 02:00:00.000',   '2011-11-05 01:59:59.997',    2011),
('2012-03-11 02:00:00.000',   '2012-11-03 01:59:59.997',    2012),
('2013-03-10 02:00:00.000',   '2013-11-02 01:59:59.997',    2013),
('2014-03-09 02:00:00.000',   '2014-11-01 01:59:59.997',    2014),
('2015-03-08 02:00:00.000',   '2015-10-31 01:59:59.997',    2015),
('2016-03-13 02:00:00.000',   '2016-11-05 01:59:59.997',    2016),
('2017-03-12 02:00:00.000',   '2017-11-04 01:59:59.997',    2017),
('2018-03-11 02:00:00.000',   '2018-11-03 01:59:59.997',    2018),
('2019-03-10 02:00:00.000',   '2019-11-02 01:59:59.997',    2019),
('2020-03-08 02:00:00.000',   '2020-10-31 01:59:59.997',    2020),
('2021-03-14 02:00:00.000',   '2021-11-06 01:59:59.997',    2021),
('2022-03-13 02:00:00.000',   '2022-11-05 01:59:59.997',    2022),
('2023-03-12 02:00:00.000',   '2023-11-04 01:59:59.997',    2023),
('2024-03-10 02:00:00.000',   '2024-11-02 01:59:59.997',    2024),
('2025-03-09 02:00:00.000',   '2025-11-01 01:59:59.997',    2025),
('1967-04-30 02:00:00.000',   '1967-10-29 01:59:59.997',    1967),
('1968-04-28 02:00:00.000',   '1968-10-27 01:59:59.997',    1968),
('1969-04-27 02:00:00.000',   '1969-10-26 01:59:59.997',    1969),
('1970-04-26 02:00:00.000',   '1970-10-25 01:59:59.997',    1970),
('1971-04-25 02:00:00.000',   '1971-10-31 01:59:59.997',    1971),
('1972-04-30 02:00:00.000',   '1972-10-29 01:59:59.997',    1972),
('1973-04-29 02:00:00.000',   '1973-10-28 01:59:59.997',    1973),
('1974-01-06 02:00:00.000',   '1974-10-27 01:59:59.997',    1974),
('1975-02-23 02:00:00.000',   '1975-10-26 01:59:59.997',    1975),
('1976-04-25 02:00:00.000',   '1976-10-31 01:59:59.997',    1976),
('1977-04-24 02:00:00.000',   '1977-10-31 01:59:59.997',    1977),
('1978-04-30 02:00:00.000',   '1978-10-29 01:59:59.997',    1978),
('1979-04-29 02:00:00.000',   '1979-10-28 01:59:59.997',    1979),
('1980-04-27 02:00:00.000',   '1980-10-26 01:59:59.997',    1980),
('1981-04-26 02:00:00.000',   '1981-10-25 01:59:59.997',    1981),
('1982-04-25 02:00:00.000',   '1982-10-25 01:59:59.997',    1982),
('1983-04-24 02:00:00.000',   '1983-10-30 01:59:59.997',    1983),
('1984-04-29 02:00:00.000',   '1984-10-28 01:59:59.997',    1984),
('1985-04-28 02:00:00.000',   '1985-10-27 01:59:59.997',    1985),
('1986-04-27 02:00:00.000',   '1986-10-26 01:59:59.997',    1986),
('1987-04-05 02:00:00.000',   '1987-10-25 01:59:59.997',    1987),
('1988-04-03 02:00:00.000',   '1988-10-30 01:59:59.997',    1988),
('1989-04-02 02:00:00.000',   '1989-10-29 01:59:59.997',    1989),
('1990-04-01 02:00:00.000',   '1990-10-28 01:59:59.997',    1990),
('1991-04-07 02:00:00.000',   '1991-10-27 01:59:59.997',    1991),
('1992-04-05 02:00:00.000',   '1992-10-25 01:59:59.997',    1992),
('1993-04-04 02:00:00.000',   '1993-10-31 01:59:59.997',    1993),
('1994-04-03 02:00:00.000',   '1994-10-30 01:59:59.997',    1994),
('1995-04-02 02:00:00.000',   '1995-10-29 01:59:59.997',    1995),
('1996-04-07 02:00:00.000',   '1996-10-27 01:59:59.997',    1996),
('1997-04-06 02:00:00.000',   '1997-10-26 01:59:59.997',    1997),
('1998-04-05 02:00:00.000',   '1998-10-25 01:59:59.997',    1998),
('1999-04-04 02:00:00.000',   '1999-10-31 01:59:59.997',    1999),
('2000-04-02 02:00:00.000',   '2000-10-29 01:59:59.997',    2000)
GO

이제 미국 표준 시간대별로 함수를 만듭니다.이것은 unix 시간이 밀리초 단위라고 가정하고 있습니다.초단위의 경우는, /1000 를 코드에서 삭제합니다.

태평양의

create function [dbo].[UnixTimeToPacific] 
 (@unixtime bigint)
   returns datetime
   as
   begin
     declare @pacificdatetime datetime
     declare @interimdatetime datetime = dateadd(s, @unixtime/1000, '1970-01-01')
     select  @pacificdatetime =  dateadd(hour,case when @interimdatetime between begin_date and end_date then -7 else -8 end  ,@interimdatetime)
     from cfg_day_light_savings_time  where  year_date = datepart(year,@interimdatetime)
     if @pacificdatetime is null 
       select @pacificdatetime= dateadd(hour, -7, @interimdatetime)
return @pacificdatetime    
end

동부

create function [dbo].[UnixTimeToEastern] 
 (@unixtime bigint)
   returns datetime
   as
   begin
     declare @easterndatetime datetime
     declare @interimdatetime datetime = dateadd(s, @unixtime/1000, '1970-01-01')
     select  @easterndatetime =  dateadd(hour,case when @interimdatetime between begin_date and end_date then -4 else -5 end  ,@interimdatetime)
     from cfg_day_light_savings_time  where  year_date = datepart(year,@interimdatetime)
     if @easterndatetime is null 
       select @easterndatetime= dateadd(hour, -4, @interimdatetime)
return @easterndatetime    
end

중앙의

create function [dbo].[UnixTimeToCentral] 
 (@unixtime bigint)
   returns datetime
   as
   begin
     declare @centraldatetime datetime
     declare @interimdatetime datetime = dateadd(s, @unixtime/1000, '1970-01-01')
     select  @centraldatetime =  dateadd(hour,case when @interimdatetime between begin_date and end_date then -5 else -6 end  ,@interimdatetime)
     from cfg_day_light_savings_time  where  year_date = datepart(year,@interimdatetime)
     if @centraldatetime is null 
       select @centraldatetime= dateadd(hour, -5, @interimdatetime)
return @centraldatetime    
end

산

create function [dbo].[UnixTimeToMountain] 
 (@unixtime bigint)
   returns datetime
   as
   begin
     declare @mountaindatetime datetime
     declare @interimdatetime datetime = dateadd(s, @unixtime/1000, '1970-01-01')
     select  @mountaindatetime =  dateadd(hour,case when @interimdatetime between begin_date and end_date then -6 else -7 end  ,@interimdatetime)
     from cfg_day_light_savings_time  where  year_date = datepart(year,@interimdatetime)
     if @mountaindatetime is null 
       select @mountaindatetime= dateadd(hour, -6, @interimdatetime)
return @mountaindatetime    
end

하와이

create function [dbo].[UnixTimeToHawaii] 
 (@unixtime bigint)
   returns datetime
   as
   begin
     declare @hawaiidatetime datetime
     declare @interimdatetime datetime = dateadd(s, @unixtime/1000, '1970-01-01')
     select  @hawaiidatetime =  dateadd(hour,-10,@interimdatetime)
     from cfg_day_light_savings_time  where  year_date = datepart(year,@interimdatetime)

return @hawaiidatetime    
end

애리조나 주

create function [dbo].[UnixTimeToArizona] 
 (@unixtime bigint)
   returns datetime
   as
   begin
     declare @arizonadatetime datetime
     declare @interimdatetime datetime = dateadd(s, @unixtime/1000, '1970-01-01')
     select  @arizonadatetime =  dateadd(hour,-7,@interimdatetime)
     from cfg_day_light_savings_time  where  year_date = datepart(year,@interimdatetime)

return @arizonadatetime    
end

알래스카

create function [dbo].[UnixTimeToAlaska] 
 (@unixtime bigint)
   returns datetime
   as
   begin
     declare @alaskadatetime datetime
     declare @interimdatetime datetime = dateadd(s, @unixtime/1000, '1970-01-01')
     select  @alaskadatetime =  dateadd(hour,case when @interimdatetime between begin_date and end_date then -8 else -9 end  ,@interimdatetime)
     from cfg_day_light_savings_time  where  year_date = datepart(year,@interimdatetime)
     if @alaskadatetime is null 
       select @alaskadatetime= dateadd(hour, -8, @interimdatetime)
return @alaskadatetime    
end

//BIGINT UNIX TIMESTAMP CONVERSION upto Millisecond Accuracy
CREATE FUNCTION [dbo].[ConvertUnixTimestamp] (@Datetime [BIGINT]) RETURNS DATETIME
AS
BEGIN

    RETURN DATEADD(MILLISECOND, cast(@Datetime as bigint) % 1000, 
    DATEADD(SECOND, (cast(@Datetime as bigint) / 1000)%60, 
    DATEADD(MINUTE, ((cast(@Datetime as bigint) / 1000)/60)%60, 
    DATEADD(HOUR, ((cast(@Datetime as bigint) / 1000)/60)/60, '19700101'))))
END

저도 이 문제에 직면해야 했어요.안타깝게도 32비트 정수 캐스팅으로 인해 2038년 이후 날짜에 도달할 수 없기 때문에 (여기와 수십 페이지의) 어떤 답변도 만족스럽지 못했습니다.

결국 제게 효과가 있었던 해결책은float변수들, 그래서 나는 적어도 최대 날짜를 가질 수 있었다.2262-04-11T23:47:16.854775849그래도 이게 다 커버하는 건 아니고datetime도메인은 내 요구에 충분하며 같은 문제를 겪고 있는 다른 사람들에게 도움을 줄 수 있습니다.

-- date variables
declare @ts bigint; -- 64 bit time stamp, 100ns precision
declare @d datetime2(7) = GETUTCDATE(); -- 'now'
-- select @d = '2262-04-11T23:47:16.854775849'; -- this would be the max date

-- constants:
declare @epoch datetime2(7) = cast('1970-01-01T00:00:00' as datetime2(7));
declare @epochdiff int = 25567; -- = days between 1900-01-01 and 1970-01-01
declare @ticksofday bigint = 864000000000; -- = (24*60*60*1000*1000*10)

-- helper variables:
declare @datepart float;
declare @timepart float;
declare @restored datetime2(7);

-- algorithm:
select @ts = DATEDIFF_BIG(NANOSECOND, @epoch, @d) / 100; -- 'now' in ticks according to unix epoch
select @timepart = (@ts % @ticksofday) / @ticksofday; -- extract time part and scale it to fractional part (i. e. 1 hour is 1/24th of a day)
select @datepart = (@ts - @timepart) / @ticksofday; -- extract date part and scale it to fractional part
select @restored = cast(@epochdiff + @datepart + @timepart as datetime); -- rebuild parts to a datetime value

-- query original datetime, intermediate timestamp and restored datetime for comparison
select
  @d original,
  @ts unix64,
  @restored restored
;

-- example result for max date:
-- +-----------------------------+-------------------+-----------------------------+
-- | original                    | unix64            | restored                    |
-- +-----------------------------+-------------------+-----------------------------+
-- | 2262-04-11 23:47:16.8547758 | 92233720368547758 | 2262-04-11 23:47:16.8533333 |
-- +-----------------------------+-------------------+-----------------------------+

고려해야 할 점이 몇 가지 있습니다.

  • 제 경우 100ns 정밀도가 요구되지만 64비트 UNIX 타임스탬프의 표준 해상도인 것 같습니다.다른 해상도를 사용할 경우 조정해야 합니다.@ticksofday알고리즘의 첫 번째 행이 표시됩니다.
  • 시간대 등에 문제가 있는 다른 시스템을 사용하고 있는데, 항상 UTC를 사용하는 것이 가장 좋은 해결책이라는 것을 알게 되었습니다.필요에 따라 다를 수 있습니다.
  • 1900-01-01의 시작일입니다.datetime2시대와 마찬가지로1970-01-01unix 타임스탬프의 경우.
  • float는, 2038년의 문제나 정수 오버플로등의 해결에 도움이 되었습니다만, 부동 소수점 숫자는 퍼포먼스가 그다지 높지 않고, 대량의 타임스탬프의 처리 속도가 저하하는 경우가 있습니다.또한 위의 최대 날짜에 대한 예제 결과 비교에서 볼 수 있듯이, 플로트는 반올림 오류로 인해 정밀도를 잃을 수 있습니다(여기서 오차는 약 1.4425ms).
  • 알고리즘의 마지막 줄에는 다음과 같은 캐스트가 있습니다.datetime유감스럽게도 숫자 값에서 다음 값까지의 명시적 캐스트는 없습니다.datetime2허용됩니다만, 숫자를 할당하는 것은 허용됩니다.datetime으로 「이러다」, 「이러다」에 .datetime2SQL Server에 대해 설명하겠습니다. 중 하나가 이다.dateadd_big() 또는 에서 "function"을 합니다.datetime2이 허용됩니다.datetime허가되지 않을 것이기 때문에, 이것은 깨지거나 언젠가 더 쉬운 길이 올지도 모릅니다.

GMT의 경우 가장 쉬운 방법은 다음과 같습니다.

Select dateadd(s, @UnixTime+DATEDIFF (S, GETUTCDATE(), GETDATE()), '1970-01-01')

CREATE FUNCTION dbo.ConvertUnixToDateTime(@Datetime BIGINT)
RETURNS DATETIME
AS
BEGIN 
    RETURN (SELECT DATEADD(second,@Datetime, CAST('1970-01-01' AS datetime)))
END;
GO

더 나아요? 이 함수는 밀리초 단위의 유니크타임을 날짜 시간으로 변환합니다.밀리초 동안 손실되지만 필터링에 매우 유용합니다.

CREATE FUNCTION [dbo].[UnixTimestampToGMTDatetime] 
(@UnixTimestamp bigint)
RETURNS datetime
AS
BEGIN
       DECLARE @GMTDatetime datetime
       select @GMTDatetime = 
       CASE
       WHEN dateadd(ss, @UnixTimestamp/1000, '1970-01-01') 
       BETWEEN 
           Convert(DATETIME, Convert(VARCHAR(4), Year(dateadd(ss, @UnixTimestamp/1000, '1970-01-01') )) + '-03-' + Convert(VARCHAR(2), (31 - (5 * Year(dateadd(ss, @UnixTimestamp/1000, '1970-01-01') )/4 + 4) % 7)) + ' 01:00:00', 20)
       AND
           Convert(DATETIME, Convert(VARCHAR(4), Year(dateadd(ss, @UnixTimestamp/1000, '1970-01-01') )) + '-10-' + Convert(VARCHAR(2), (31 - (5 * Year(dateadd(ss, @UnixTimestamp/1000, '1970-01-01') )/4 + 1) % 7)) + ' 02:00:00', 20)
       THEN Dateadd(hh, 1, dateadd(ss, @UnixTimestamp/1000, '1970-01-01'))
       ELSE Dateadd(hh, 0, dateadd(ss, @UnixTimestamp/1000, '1970-01-01'))
       END
RETURN @GMTDatetime    
END

솔루션은 다음과 같습니다.

DECLARE @UnixTimeStamp bigint = 1564646400000 /*2019-08-01 11:00 AM*/

DECLARE @LocalTimeOffset bigint = DATEDIFF(MILLISECOND, GETDATE(), GETUTCDATE());
DECLARE @AdjustedTimeStamp bigint = @UnixTimeStamp - @LocalTimeOffset;
SELECT [DateTime] = DATEADD(SECOND, @AdjustedTimeStamp % 1000, DATEADD(SECOND, @AdjustedTimeStamp / 1000, '19700101'));

부터 2038년 이후부터 2038년 이후부터 순서로 할 수 .dateadd(). UTC를 반환하지만 예를 들어, 를 참조하십시오. 시간대 및 DST 처리에 대한 Kenny의 답변.

IF OBJECT_ID('dbo.fn_ConvertUnixToDateTime') IS NULL
  EXEC ('CREATE function dbo.fn_ConvertUnixToDateTime() returns int AS begin RETURN 0 end;')
GO
go 
alter function dbo.fn_ConvertUnixToDateTime (@unixTimestamp BIGINT)
RETURNS DATETIME
AS
/* 
    Converts unix timestamp to utc datetime. 

    To work with larger timestamps it does a two-part add, since dateadd()
    function only allows you to add int values, not bigint. 
*/ 
BEGIN
    RETURN (SELECT DATEADD( second
                        ,   @unixTimestamp % 3600
                        ,   dateadd(    hour
                                    ,   @unixTimestamp / 3600
                                    ,   CAST('1970-01-01 00:00:00' AS datetime)
                                    )
                        )
            )

END;

SQL 서버에서 다음을 시도할 수 있습니다.

날짜 추가(S, CAST(CONVERT(INT, CONVERT(INT, CONVERT(VARBINAL, SYSTERLING)('Timestramp', 1, 8, 2)) AS NURICAL(20), '1970-01-01-01')을 선택합니다.

예:

날짜 추가(S, CAST(INT, CONVERT(INT, CONVERT(VARBINARY, SUBSTRING)('6369841c05df306d5dc81914', 1, 8), 2)를 숫자(20), '1970-01-01'로 선택)

@syslog, @syslog-little

나는 다니엘의 답이 틀렸다고 생각해.

그의 논리는 윤년의 초만을 사용하여 계산한다.따라서 변환된 값이 올바르지 않습니다.

다음과 같은 기능이 필요하다고 생각합니다.

CREATE OR ALTER FUNCTION dbo.convert_unixtime_to_utc_datetime
(
  @source_unix_time AS BIGINT
)
RETURNS DATETIME2
  BEGIN
    
    DECLARE @remaining_unix_time AS BIGINT = @source_unix_time
    DECLARE @max_int_value AS BIGINT = 2147483647
    DECLARE @unixtime_base_year AS INT = 1970
    DECLARE @target_year AS INT = @unixtime_base_year
    DECLARE @year_offset AS INT = 0
    DECLARE @subtract_value AS BIGINT = 0
    DECLARE @calc_base_datetime AS DATETIME2
    DECLARE @seconds_per_day AS BIGINT = (60 /* seconds */ * 60 /* minutes */ * 24 /* hours */)
    
    WHILE (1 = 1)
      BEGIN
        IF @remaining_unix_time <= @max_int_value
          BREAK
        
        IF ((@target_year % 400 = 0) OR ((@target_year % 4 = 0) AND (@target_year % 100 != 0)))
          SET @subtract_value = (@seconds_per_day * 366 /* days */)
        ELSE
          SET @subtract_value = (@seconds_per_day * 365 /* days */)

        SET @remaining_unix_time -= @subtract_value
        SET @target_year += 1
        SET @year_offset += 1
        
      END
  
    SET @calc_base_datetime = DATETIME2FROMPARTS(@unixtime_base_year + @year_offset, 1, 1, 0, 0, 0, 0, 0)

    RETURN DATEADD(SECOND, @remaining_unix_time, @calc_base_datetime)
  END
;

다음과 같이 컴포넌트를 사용하여 엣지 케이스를 확인하였습니다.

  • DDL
CREATE TABLE test_convert_unixtime_table (
  id BIGINT NOT NULL
  , description nvarchar(max) NOT NULL
  , source_utc_datetime datetime2 NOT NULL
  , source_unixtime BIGINT NOT NULL
  , CONSTRAINT pkc_test_convert_unixtime_table PRIMARY KEY (id)
) ;

ALTER TABLE test_convert_unixtime_table ADD CONSTRAINT idx_test_convert_unixtime_table_1
  UNIQUE (source_unixtime) ;
  • DML
INSERT INTO test_convert_unixtime_table (id, description, source_utc_datetime, source_unixtime) VALUES (2, 'threshold of 2038 year problem', '2038/01/19 03:14:07', 2147483647);
INSERT INTO test_convert_unixtime_table (id, description, source_utc_datetime, source_unixtime) VALUES (3, 'threshold of 2038 year problem + 1 second', '2038/01/19 03:14:08', 2147483648);
INSERT INTO test_convert_unixtime_table (id, description, source_utc_datetime, source_unixtime) VALUES (4, 'leap year - 1 year before 2038 
the first day of the year', '2035/01/01 00:00:00', 2051222400);
INSERT INTO test_convert_unixtime_table (id, description, source_utc_datetime, source_unixtime) VALUES (5, 'leap year - 1 year before 2038 
the end of Feburary', '2035/02/28 23:59:59', 2056319999);
INSERT INTO test_convert_unixtime_table (id, description, source_utc_datetime, source_unixtime) VALUES (6, 'leap year - 1 year before 2038 
the first day of March', '2035/03/01 00:00:00', 2056320000);
INSERT INTO test_convert_unixtime_table (id, description, source_utc_datetime, source_unixtime) VALUES (7, 'leap year - 1 year before 2038 
new year's eve', '2035/12/31 23:59:59', 2082758399);
INSERT INTO test_convert_unixtime_table (id, description, source_utc_datetime, source_unixtime) VALUES (8, 'leap year before 2038 
the first day of the year', '2036/01/01 00:00:00', 2082758400);
INSERT INTO test_convert_unixtime_table (id, description, source_utc_datetime, source_unixtime) VALUES (9, 'leap year before 2038 
the end of Feburary', '2036/02/29 23:59:59', 2087942399);
INSERT INTO test_convert_unixtime_table (id, description, source_utc_datetime, source_unixtime) VALUES (10, 'leap year before 2038 
the first day of March', '2036/03/01 00:00:00', 2087942400);
INSERT INTO test_convert_unixtime_table (id, description, source_utc_datetime, source_unixtime) VALUES (11, 'leap year before 2038 
new year's eve', '2036/12/31 23:59:59', 2114380799);
INSERT INTO test_convert_unixtime_table (id, description, source_utc_datetime, source_unixtime) VALUES (12, 'leap year + 1 year before 2038 
the first day of the year', '2037/01/01 00:00:00', 2114380800);
INSERT INTO test_convert_unixtime_table (id, description, source_utc_datetime, source_unixtime) VALUES (13, 'leap year + 1 year before 2038 
the end of Feburary', '2037/02/28 23:59:59', 2119478399);
INSERT INTO test_convert_unixtime_table (id, description, source_utc_datetime, source_unixtime) VALUES (14, 'leap year + 1 year before 2038 
the first day of March', '2037/03/01 00:00:00', 2119478400);
INSERT INTO test_convert_unixtime_table (id, description, source_utc_datetime, source_unixtime) VALUES (15, 'leap year + 1 year before 2038 
new year's eve', '2037/12/31 23:59:59', 2145916799);
INSERT INTO test_convert_unixtime_table (id, description, source_utc_datetime, source_unixtime) VALUES (16, 'leap year - 1 year after 2038 
the first day of the year', '2039/01/01 00:00:00', 2177452800);
INSERT INTO test_convert_unixtime_table (id, description, source_utc_datetime, source_unixtime) VALUES (17, 'leap year - 1 year after 2038 
the end of Feburary', '2039/02/28 23:59:59', 2182550399);
INSERT INTO test_convert_unixtime_table (id, description, source_utc_datetime, source_unixtime) VALUES (18, 'leap year - 1 year after 2038 
the first day of March', '2039/03/01 00:00:00', 2182550400);
INSERT INTO test_convert_unixtime_table (id, description, source_utc_datetime, source_unixtime) VALUES (19, 'leap year - 1 year after 2038 
new year's eve', '2039/12/31 23:59:59', 2208988799);
INSERT INTO test_convert_unixtime_table (id, description, source_utc_datetime, source_unixtime) VALUES (20, 'leap year after 2038 
the first day of the year', '2040/01/01 00:00:00', 2208988800);
INSERT INTO test_convert_unixtime_table (id, description, source_utc_datetime, source_unixtime) VALUES (21, 'leap year after 2038 
the end of Feburary', '2040/02/29 23:59:59', 2214172799);
INSERT INTO test_convert_unixtime_table (id, description, source_utc_datetime, source_unixtime) VALUES (22, 'leap year after 2038 
the first day of March', '2040/03/01 00:00:00', 2214172800);
INSERT INTO test_convert_unixtime_table (id, description, source_utc_datetime, source_unixtime) VALUES (23, 'leap year after 2038 
new year's eve', '2040/12/31 23:59:59', 2240611199);
INSERT INTO test_convert_unixtime_table (id, description, source_utc_datetime, source_unixtime) VALUES (24, 'leap year + 1 year after 2038 
the first day of the year', '2041/01/01 00:00:00', 2240611200);
INSERT INTO test_convert_unixtime_table (id, description, source_utc_datetime, source_unixtime) VALUES (25, 'leap year + 1 year after 2038 
the end of Feburary', '2041/02/28 23:59:59', 2245708799);
INSERT INTO test_convert_unixtime_table (id, description, source_utc_datetime, source_unixtime) VALUES (26, 'leap year + 1 year after 2038 
the first day of March', '2041/03/01 00:00:00', 2245708800);
INSERT INTO test_convert_unixtime_table (id, description, source_utc_datetime, source_unixtime) VALUES (27, 'leap year + 1 year after 2038 
new year's eve', '2041/12/31 23:59:59', 2272147199);
  • 쿼리
SELECT 
  *
  , dbo.convert_unixtime_to_utc_datetime(source_unixtime) AS [by_myfunc]
  , dbo.func_by_daniel_little(source_unixtime) AS [by_daniel_little]
FROM
  test_convert_unixtime_table
ORDER BY
  id;
  • 결과 세트
|id |description                                             |source_utc_datetime    |source_unixtime|by_myfunc              |by_daniel_little       |
|---|--------------------------------------------------------|-----------------------|---------------|-----------------------|-----------------------|
|1  |threshold of 2038 year problem - 1 second               |2038/01/19 03:14:06.000|2,147,483,646  |2038/01/19 03:14:06.000|2037/11/30 03:14:06.000|
|2  |threshold of 2038 year problem                          |2038/01/19 03:14:07.000|2,147,483,647  |2038/01/19 03:14:07.000|2037/11/30 03:14:07.000|
|3  |threshold of 2038 year problem + 1 second               |2038/01/19 03:14:08.000|2,147,483,648  |2038/01/19 03:14:08.000|2037/11/30 03:14:08.000|
|4  |leap year - 1 year before 2038 the first day of the year|2035/01/01 00:00:00.000|2,051,222,400  |2035/01/01 00:00:00.000|2034/11/14 00:00:00.000|
|5  |leap year - 1 year before 2038 the end of Feburary      |2035/02/28 23:59:59.000|2,056,319,999  |2035/02/28 23:59:59.000|2035/01/10 23:59:59.000|
|6  |leap year - 1 year before 2038 the first day of March   |2035/03/01 00:00:00.000|2,056,320,000  |2035/03/01 00:00:00.000|2035/01/11 00:00:00.000|
|7  |leap year - 1 year before 2038 new year's eve           |2035/12/31 23:59:59.000|2,082,758,399  |2035/12/31 23:59:59.000|2035/11/12 23:59:59.000|
|8  |leap year before 2038 the first day of the year         |2036/01/01 00:00:00.000|2,082,758,400  |2036/01/01 00:00:00.000|2035/11/13 00:00:00.000|
|9  |leap year before 2038 the end of Feburary               |2036/02/29 23:59:59.000|2,087,942,399  |2036/02/29 23:59:59.000|2036/01/10 23:59:59.000|
|10 |leap year before 2038 the first day of March            |2036/03/01 00:00:00.000|2,087,942,400  |2036/03/01 00:00:00.000|2036/01/11 00:00:00.000|
|11 |leap year before 2038 new year's eve                    |2036/12/31 23:59:59.000|2,114,380,799  |2036/12/31 23:59:59.000|2036/11/11 23:59:59.000|
|12 |leap year + 1 year before 2038 the first day of the year|2037/01/01 00:00:00.000|2,114,380,800  |2037/01/01 00:00:00.000|2036/11/12 00:00:00.000|
|13 |leap year + 1 year before 2038 the end of Feburary      |2037/02/28 23:59:59.000|2,119,478,399  |2037/02/28 23:59:59.000|2037/01/09 23:59:59.000|
|14 |leap year + 1 year before 2038 the first day of March   |2037/03/01 00:00:00.000|2,119,478,400  |2037/03/01 00:00:00.000|2037/01/10 00:00:00.000|
|15 |leap year + 1 year before 2038 new year's eve           |2037/12/31 23:59:59.000|2,145,916,799  |2037/12/31 23:59:59.000|2037/11/11 23:59:59.000|
|16 |leap year - 1 year after 2038 the first day of the year |2039/01/01 00:00:00.000|2,177,452,800  |2039/01/01 00:00:00.000|2038/11/11 00:00:00.000|
|17 |leap year - 1 year after 2038 the end of Feburary       |2039/02/28 23:59:59.000|2,182,550,399  |2039/02/28 23:59:59.000|2039/01/07 23:59:59.000|
|18 |leap year - 1 year after 2038 the first day of March    |2039/03/01 00:00:00.000|2,182,550,400  |2039/03/01 00:00:00.000|2039/01/08 00:00:00.000|
|19 |leap year - 1 year after 2038 new year's eve            |2039/12/31 23:59:59.000|2,208,988,799  |2039/12/31 23:59:59.000|2039/11/09 23:59:59.000|
|20 |leap year after 2038 the first day of the year          |2040/01/01 00:00:00.000|2,208,988,800  |2040/01/01 00:00:00.000|2039/11/10 00:00:00.000|
|21 |leap year after 2038 the end of Feburary                |2040/02/29 23:59:59.000|2,214,172,799  |2040/02/29 23:59:59.000|2040/01/07 23:59:59.000|
|22 |leap year after 2038 the first day of March             |2040/03/01 00:00:00.000|2,214,172,800  |2040/03/01 00:00:00.000|2040/01/08 00:00:00.000|
|23 |leap year after 2038 new year's eve                     |2040/12/31 23:59:59.000|2,240,611,199  |2040/12/31 23:59:59.000|2040/11/08 23:59:59.000|
|24 |leap year + 1 year after 2038 the first day of the year |2041/01/01 00:00:00.000|2,240,611,200  |2041/01/01 00:00:00.000|2040/11/09 00:00:00.000|
|25 |leap year + 1 year after 2038 the end of Feburary       |2041/02/28 23:59:59.000|2,245,708,799  |2041/02/28 23:59:59.000|2041/01/06 23:59:59.000|
|26 |leap year + 1 year after 2038 the first day of March    |2041/03/01 00:00:00.000|2,245,708,800  |2041/03/01 00:00:00.000|2041/01/07 00:00:00.000|
|27 |leap year + 1 year after 2038 new year's eve            |2041/12/31 23:59:59.000|2,272,147,199  |2041/12/31 23:59:59.000|2041/11/08 23:59:59.000|

함수에 의해 반환된 값은 모두 소스 datetime과 일치하지만 Daniel의 논리에 의해 재실행된 값은 소스 datetime과 모두 일치하지 않습니다.

언급URL : https://stackoverflow.com/questions/2904256/how-can-i-convert-bigint-unix-timestamp-to-datetime-in-sql-server

반응형

'prosource' 카테고리의 다른 글

List vs Linked List를 사용해야 하는 경우  (0) 2023.04.13
WPF에서 마우스 이벤트에 대해 제어를 투과적으로 하는 방법이 있습니까?  (0) 2023.04.13
두 리비전 간에 변경된 파일 표시  (0) 2023.04.13
Git이 자꾸 내 ssh 키 패스프레이즈를 묻는다.  (0) 2023.04.13
자동으로 종료되는 WPF 목록 상자 스크롤  (0) 2023.04.13

'prosource'의 다른글

  • 현재글SQL Server에서 bigint(UNIX 타임스탬프)를 datetime으로 변환하려면 어떻게 해야 합니까?

관련글

댓글

티스토리툴바