How can I convert bigint UNIX timestamp to datetime in SQL Server

Question

How can I convert UNIX timestamp  bigint  to DateTime in SQL Server

User · Answer

try   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

User · Answer

This will do it   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     Instead of  precision  use  ss ms or mcs according to the precision of the timestamp  Bigint is capable to hold microsecond precision

User · Answer

Adding n seconds to 1970-01-01 will give you a UTC date because n  the Unix timestamp  is the number of seconds that have elapsed since 00 00 00 Coordinated Universal Time  UTC   Thursday  1 January 1970   In SQL Server 2016  you can convert one time zone to another using AT TIME ZONE  You just need to know the name of the time zone in Windows standard format   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     Or simply   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     Notes    You can chop off the timezone information by casting DATETIMEOFFSET to DATETIME  The conversion takes daylight savings time into account  Pacific time was UTC-08 00 on January 2018 and UTC-07 00 on Jun 2018

User · Answer

Better  This function converts unixtime in milliseconds to datetime  It s lost milliseconds  but still very useful for filtering   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

User · Answer

This is building off the work Daniel Little did for this question  but taking into account daylight savings time  works for dates 01-01 1902 and greater due to int limit on dateadd function    We first need to create a table that will store the date ranges for daylight savings time  source  History of time in the United States    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   Now we create a function for each American timezone  This is assuming the unix time is in milliseconds  If it is in seconds  remove the  1000 from the code   Pacific  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   Eastern  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   Central  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   Mountain  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   Hawaii  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   Arizona  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   Alaska  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

User · Answer

I had to face this problem  too  Unfortunately  none of the answers  here and in dozens of other pages  has been satisfactory to me  as I still cannot reach dates beyond the year 2038 due to 32 bit integer casts somewhere   A solution that did work for me in the end was to use float variables  so I could have at least a max date of 2262-04-11T23 47 16 854775849  Still  this doesn t cover the entire datetime domain  but it is sufficient for my needs and may help others encountering the same problem   -- 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   --  ----------------------------- ------------------- -----------------------------    There are some points to consider    100ns precision is the requirement in my case  however this seems to be the standard resolution for 64 bit unix timestamps  If you use any other resolution  you have to adjust  ticksofday and the first line of the algorithm accordingly  I m using other systems that have their problems with time zones etc  and I found the best solution for me would be always using UTC  For your needs  this may differ  1900-01-01 is the origin date for datetime2  just as is the epoch 1970-01-01 for unix timestamps  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   In the last line of the algorithm there is a cast to datetime  Unfortunately  there is no explicit cast from numeric values to datetime2 allowed  but it is allowed to cast numerics to datetime explicitly and this  in turn  is cast implicitly to datetime2  This may be correct  for now  but may change in future versions of SQL Server  Either there will be a dateadd big   function or the explicit cast to datetime2 will be allowed or the explicit cast to datetime will be disallowed  so this may either break or there may come an easier way some day

User · Answer

For GMT  here is the easiest way   Select dateadd s   UnixTime DATEDIFF  S  GETUTCDATE    GETDATE      1970-01-01

User · Answer

If anyone getting below error      Arithmetic overflow error converting expression to data type int   due to unix timestamp is in bigint  instead of int   you can use this   SELECT DATEADD S  CONVERT int LEFT 1462924862735870900  10     1970-01-01   FROM TABLE   Replace the hardcoded timestamp for your actual column with unix-timestamp  Source  MSSQL bigint Unix Timestamp to Datetime with milliseconds

User · Answer

This worked for me   Select     dateadd S   unixtime    1970-01-01   From  Table    In case any one wonders why 1970-01-01  This is called Epoch time     Below is  a quote from Wikipedia        The number of seconds that have elapsed since 00 00 00 Coordinated Universal Time  UTC   Thursday  1 January 1970  1  note 1  not counting leap seconds

User · Answer

Solution can be the following   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

User · Answer

CREATE FUNCTION dbo ConvertUnixToDateTime  Datetime BIGINT  RETURNS DATETIME AS BEGIN      RETURN  SELECT DATEADD second  Datetime  CAST  1970-01-01  AS datetime    END  GO

User · Answer

Like this  add the Unix  epoch  datetime to the base date in seconds  this will get it for now  2010-05-25 07 56 23 000    SELECT dateadd s 1274756183  19700101 05 00 00 000     If you want to go reverse  take a look at this http   wiki lessthandot com index php Epoch Date

User · Answer

DanielLittle has the easiest and most elegant answer to the specific question  However  if you are interested in converting to a specific timezone AND taking into account DST  Daylight Savings Time   the following works well  CAST DATEADD S   UnixTimestamp    1970-01-01   AT TIME ZONE  UTC  AT TIME ZONE  Pacific Standard Time  AS Datetime   Note  This solution only works on SQL Server 2016 and above  and Azure   To create a function  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  You can call the function like so  SELECT   dbo ConvertUnixTime  UnixTimestamp   FROM     YourTable

User · Answer

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

User · Answer

If the time is in milliseconds and one need to preserve them   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

User · Answer

Test this   Sql server   SELECT dateadd S  timestamp   1970-01-01 00 00 00         FROM  your table   MySql server   SELECT   from unixtime timestamp   FROM    your table   http   www w3resource com mysql date-and-time-functions mysql-from unixtime-function php

[sql] How can I convert bigint (UNIX timestamp) to datetime in SQL Server?

Examples related to sql

Examples related to sql-server

Examples related to tsql

Examples related to datetime

Examples related to unix-timestamp