IT

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

lottoking 2020. 9. 8. 08:14
반응형

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

SQL Server에서 UNIX 타임 스탬프 (bigint)를 DateTime으로 어떻게 변환 할 수 있습니까?

시험 :

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

이것은 나를 위해 일했습니다.

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

왜 1970-01-01, 보유한 시대라고하는 궁금한 사람이 있다면 아래는 위키 백과의 인용문입니다.

1970 년 1 월 1 일 목요일 00:00:00 UTC (협정 세계시) 이후 경과 된 초 수 [1] [참고 1] 윤초를 계산하지

이렇게

Unix (epoch) datetime을 기본 날짜에 초 단위로 추가하십시오.

이것은 당분간 얻을 것입니다 (2010-05-25 07 : 56 : 23.000)

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

가고 싶다면 반대로 http://wiki.lessthandot.com/index.php/Epoch_Date를 살펴보십시오 .

유닉스 타임 스탬프로 인해 " 산술 오버플로 오류를 int 데이터 유형으로 변환하는 중 "을받는 사람이 bigint (int 대신)에있는 경우 다음을 사용할 수 있습니다.

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

실제 열의 하드 코딩 된 타임 스탬프를 유닉스 타임 스탬프로 대체합니다.

출처 : MSSQL bigint Unix Timestamp to Datetime with milliseconds

이렇게하면됩니다.

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는 마이크로 초를 사용합니다.

이것을 테스트하십시오 :

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

이 Daniel Little이 질문에 대해 한 작업을 기반으로하지만 절약 절약 시간을 고려합니다 (dateadd 함수의 int 제한으로 인해 1902 년 1 월 1 일 이후의 날짜에서 작동 함).

먼저 일광 절약 시간의 날짜 범위를 저장할 테이블을 만들어야합니다 (출처 : 미국 시간 기록 ).

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

이제 각 미국 고유에 대한 함수를 만듭니다. 이 유닉스 시간이 밀리 초 단위라고 가정합니다. 초 단위 인 경우 코드에서 / 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

보다 나은? 이 함수는 밀리 초 단위의 unixtime을 datetime으로 변환합니다. 밀리 초가는 여전히 필터링에 매우 유용합니다.

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

에 n 초를 추가 1970-01-01하면 UTC 날짜가 표시됩니다. 왜냐하면 Unix 타임 스탬프 인 n 은 1970 년 1 월 1 일 목요일 00:00:00 UTC (협정 세계시) 이후 경과 한 초 수이기 때문 입니다.

SQL Server 2016에서는 .NET을 사용하여 한 표준 제공을 다른 표준 언어로 변환 할 수 있습니다 . Windows 표준 형식의 표준 제공 이름 만 있으면 좋습니다.AT TIME ZONE

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입니다.
//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 비트 유닉스 타임 스탬프의 표준 해상도 인 것 같습니다. 다른 해상도를 사용하는 경우 @ticksofday그에 따라 알고리즘의 첫 번째 줄 을 조정해야합니다 .
  • 시간대 등에 문제가있는 다른 시스템을 사용하고 있습니다. 가장 좋은 해결책은 항상 UTC를 사용하는 것입니다. 필요에 따라 다를 수 있습니다.
  • 1900-01-01유닉스 타임 스탬프 datetime2의 에포크와 마찬가지로의 시작 날짜입니다 1970-01-01.
  • floats helped me to solve the year-2038-problem and integer overflows and such, but keep in mind that floating point numbers are not very performant and may slow down processing of a big amount of timestamps. Also, floats may lead to loss of precision due to roundoff errors, as you can see in the comparison of the example results for the max date above (here, the error is about 1.4425ms).
  • 알고리즘의 마지막 줄에는 datetime. 안타깝게도 숫자 값에서 datetime2허용 으로의 명시 적 캐스트는 없지만 숫자를 명시 적으로 캐스트 할 수 있으며, 이는 datetime암시 적으로로 캐스트됩니다 datetime2. 현재로서는 정확할 수 있지만 SQL Server의 향후 버전에서는 변경 될 수 있습니다. dateadd_big()함수가 있거나 명시 적 캐스트 datetime2가 허용되거나 명시 적 캐스트 datetime가 허용되지 않으므로 중단되거나 발생할 수 있습니다. 언젠가 더 쉬운 방법.

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

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'))

해결책은 다음과 같습니다.

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'));

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

반응형

'IT' 카테고리의 다른 글

Ruby on Rails : rake db : migrate 마이그레이션을 되돌리려면 어떻게해야합니까?  (0) 2020.09.08
모바일 사파리 탭과 같은 UIScrollView 수평 페이징  (0) 2020.09.08
mongodb에 강력하지 않습니다. errno : 61 연결이 거부되었습니다.  (0) 2020.09.08
C / C ++에서 log base (2)를 작성하는 방법  (0) 2020.09.08
iPhone에서 "명령 / bin / sh가 종료 코드 1로 실패"문제를 해결하는 방법  (0) 2020.09.08

'IT'의 다른글

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

관련글

댓글

티스토리툴바