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?

Daniel Little · Accepted Answer · 2022-05-17T05:59:02.940

430

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.

The Year 2038 Problem

Furthermore the DataAdd function takes an int for the seconds to add. So if you are trying to add more than 2147483647 seconds you'll get an arithmetic overflow error. To resolve this issue you can break the addition into two calls to DateAdd one for the years and one for the remaining seconds.

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

This will let you convert a timestamp that represents a year greater than 2038.

edited May 17 '22 at 05:59

answered Nov 23 '11 at 02:59

Daniel Little

16,975
12
69
93

29

This should be marked as correct. I wish I could upvote this answer again every time I land here :) – BenDundee Sep 18 '14 at 19:22
4

@BenDundee I really agree with you. What an elegant solution. I looked all over for a formula and finally settled on one only to have to go searching again when I started running into errors a week later. Thankfully I found this one on the second go-around. – Ross Brasseaux Dec 09 '14 at 19:44
2

I have been using this solution. This formatted date was concatenad with other data, so I had a varchar... Easy! No need to bother to format those logs in the application. However, some wild time zone problems appeared! My dates were using the UTC time zone instead of the customer's time zone :( – gustavohenke Aug 08 '16 at 20:43
3

@Whitecat Don't know if you already solved your problem, but watch the casing! Maybe your database collation setting is set to something like 'SQL_Latin1_General_CP1_CS_AS', CS is the keyword here. It stands for "CaseSensitiv" therefor your code must match the casing! Another point could be that your System is MySql, than the name is date_add(). Regards ;) – Nightking Feb 11 '17 at 10:15
1

Anyone have a version of this that doesn't suffer from the 2038 problem? (DateAdd converts to an int, so this solution is vulnerable to it). https://en.wikipedia.org/wiki/Year_2038_problem – Nathan Jan 31 '18 at 10:21
8

This solution will be affected by the year 2038 problem because the dateadd function requires an int type. The documentation says "The number argument cannot exceed the range of int." https://learn.microsoft.com/en-us/sql/t-sql/functions/dateadd-transact-sql https://en.wikipedia.org/wiki/Year_2038_problem – Patrick H Mar 28 '18 at 21:26
2

Be advised that the date/time returned by this function would be UTC, not local date/time. Converting to local is a real pain in the bottom. – Salman A Sep 15 '18 at 20:05
2

this causes Arithmetic overflow due to timestamps that no longer "fit" into an int – Adam May 16 '19 at 04:20
1

NIce solution - but if the database account has restricted access (i.e. say cannot run server-side functions) this will not work. e.g. `mysql: ERROR 1370 (42000): execute command denied to user 'mydbuser'@'%' for routine 'mydbname.dateadd'` – colm.anseo Jun 03 '19 at 16:06
1

Msg 8115, Level 16, State 2, Line 36 Arithmetic overflow error converting expression to data type int. – Muhammad Saqib Dec 25 '19 at 14:57
@Nathan `SELECT DATEADD(SECOND,2147483648%60,DATEADD(MI,2147483648/60,'1970-01-01 00:00:00.000'))` – BWFC Jan 22 '20 at 11:41
Did not work with me with this value `1624279488665004` . The error was `Arithmetic overflow error converting expression to data type int.` – Sau001 Jun 28 '21 at 13:30
Thanks for the feedback, I've now addressed the 2038 problem – Daniel Little Jun 07 '22 at 01:07
Be advised that the resulting date would be UTC, not local datetime. – Salman A Jun 12 '23 at 07:15
This is a great idea, with the exception that a year is not always the same length. This gives the wrong result. You need to use a fixed-length unit of time to divide by. Broken example: `Declare @t as bigint = 2147483648; Declare @oneyear as int = 31622400; Select DateAdd(SECOND, @t % @oneyear, DateAdd(YEAR, @t / @oneyear, '1970-01-01'))` This should give a value in Jan 2038, but it gives 2037 as the year. Must be a leap year issue. Use weeks instead? – mbomb007 Jul 12 '23 at 19:02

score 62 · Answer 2 · edited Feb 26 '20 at 05:54

62

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

edited Feb 26 '20 at 05:54

shA.t

16,580
5
54
111

answered Feb 02 '17 at 14:02

julianm

2,393
1
23
24

1

Given epoch milliseconds, even better: SELECT DATEADD(ms, 1517270454852%1000, DATEADD(S, 1517270454852/1000, '1970-01-01')) – G DeMasters Jun 28 '20 at 04:21
3

thank you . this should be upvoted 1462924862735870900 TIMES!!!!!!!!!!!!!!!!!!!!! – john Aug 29 '20 at 22:06
1

Glad to help @John – julianm Aug 31 '20 at 13:20
1

I am getting `Conversion failed when converting the varchar value '1.20787e+0' to data type int` – TiyebM Nov 12 '20 at 12:12
2

Your number is in scientific notation @TiyebM. And it's not a integer value (it has decimals) – julianm Nov 12 '20 at 19:54

score 61 · Answer 3 · edited Mar 04 '15 at 23:19

61

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

edited Mar 04 '15 at 23:19

Diego

18,035
5
62
66

answered May 25 '10 at 11:57

KM.

101,727
34
178
212

3

+1 For the UTC->local conversion. Note that the summer/wintertime will still be off if you try to translate June 10th during February. – Andomar May 25 '10 at 12:07
13

-1 for the UTC->local conversion. It is not correctly handling daylight saving time. For me, this is misguiding. – Pavel Horal Sep 28 '12 at 13:04
1

+1 for the creative solution! Works great. FYI, there is a syntax error in the SQL. The semi-colon at the end of the first "DECLARE" line needs to be removed since a comma follows. – Seth Sep 17 '14 at 14:12
6

It doesn't work for me. I am trying it with 1517270400000 and getting this error: Arithmetic overflow error converting expression to data type int. – Danish Jul 05 '18 at 07:27
1

I experienced a problem when the @Datetime was out of the int range 'Arithmetic overflow error' so I post a improved version of this great solution: https://stackoverflow.com/a/53177954/687490 – Vespucci75fr Nov 06 '18 at 18:37
2

Also was getting an overflow, normally meaning that milliseconds are involved, solved simply as: select dbo.fn_ConvertToDateTime( src_column/1000 ) from src_table; – access_granted Jan 10 '19 at 04:42
1

@Danish your time of `1517270400000` is epoch *milli*-seconds. Divide by 1000 and it should work. – colm.anseo Jun 03 '19 at 15:59

score 31 · Answer 4 · answered May 25 '10 at 11:57

31

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

answered May 25 '10 at 11:57

SQLMenace

132,095
25
206
225

3

why 05:00:00 instead of 00:00:00? – Svisstack Aug 07 '17 at 11:27
4

@Svisstack the 5 hours is for timezone difference. 5:00:00 means he's GMT-5 hours – Jordy van Eijk Oct 11 '17 at 08:36
1

Works like a charm. If you need to adjust for the timezone, then certainly do that but consider this very efficient – clifton_h Apr 18 '18 at 14:51

Mohamad Hamouday · Answer 5 · 2019-03-27T15:13:24.267

14

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

edited Mar 27 '19 at 15:13

answered Dec 22 '16 at 14:46

Mohamad Hamouday

2,070
23
20

1

That's for the UTC timezone, isn't it? So if you're in a different time zone you modify it accordingly, e.g. if you're in the UTC+3 time zone you use: '1970-01-01 03:00:00' Am I guessing correctly? – luisdev May 06 '21 at 08:52

score 8 · Answer 6 · answered May 25 '10 at 11:58

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.

Salman A · Answer 7 · 2023-06-12T07:37:52.457

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 need to specify 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' AS LocalDate
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:

The conversion takes daylight savings time into account. Pacific time was UTC-08:00 on January 2018 and UTC-07:00 on Jun 2018.
You can chop off the timezone information by casting DATETIMEOFFSET to DATETIME.
For full list of windows time zone "names", probe the sys.time_zone_info view or use tzutil.

score 6 · Answer 8 · edited Feb 26 '20 at 05:56

6

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

edited Feb 26 '20 at 05:56

shA.t

16,580
5
54
111

answered Jul 02 '19 at 10:40

gotqn

42,737
46
157
243

Chris Tophski · Answer 9 · 2019-05-20T10:04:57.480

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.

score 3 · Answer 10 · answered Aug 05 '20 at 22:19

@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

How does this accommodate other UTC timezones? E.g. UTC+2 or UTC+5? — luisdev, Nov 23 '20 at 07:40
Finally an answer that takes time zones into consideration! A list of time zones in SQL server can easily be found here: https://database.guide/get-a-list-of-supported-time-zones-in-sql-server-t-sql/ — specimen, Dec 02 '21 at 09:18

score 2 · Answer 11 · answered Mar 27 '17 at 20:09

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

score 2 · Answer 12 · edited Dec 30 '18 at 19:48

//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

score 2 · Answer 13 · edited Apr 25 '20 at 18:42

2

For GMT, here is the easiest way:

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

edited Apr 25 '20 at 18:42

Fred Sobotka

5,252
22
32

answered Apr 25 '20 at 14:45

Saolin

21
1

score 2 · Answer 14 · answered Oct 28 '20 at 05:45

2

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

answered Oct 28 '20 at 05:45

Emam

63
7

score 1 · Answer 15 · edited May 20 '15 at 12:20

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

Vitaly Ilyuhin · Answer 16 · 2019-08-01T08:15:29.497

1

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

edited Aug 01 '19 at 08:15

answered Aug 01 '19 at 08:02

Vitaly Ilyuhin

21
2

score 1 · Answer 17 · answered Jan 06 '22 at 13:08

Here's the function I use, which works for dates larger than 2038 by doing a two-step dateadd(). It returns UTC, but see e.g. Kenny's answer for timezone and DST handling.

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;

score 0 · Answer 18 · answered Nov 29 '22 at 21:13

On SQL server you can try this:

SELECT dateadd(S, CAST(CONVERT(INT, CONVERT(VARBINARY, SUBSTRing('Timestramp', 1, 8), 2)) AS NUMERIC(20)), '1970-01-01')

Example:

SELECT dateadd(S, CAST(CONVERT(INT, CONVERT(VARBINARY, SUBSTRING('6369841c05df306d5dc81914', 1, 8), 2)) AS NUMERIC(20)), '1970-01-01')

score 0 · Answer 19 · answered Dec 06 '22 at 02:46

@salman , @daniel-little

I think that Daniel's answer is wrong.

His logic calculates using only seconds of leap year. So converted values are not correct.

I think that we need the function as follows:

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
;

I verified edge cases using components as follows:

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

Query

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;

ResultSet

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

Returned values by my function all match source datetime, however, Retruned values by Daniel's logic do not all match source datetime.

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

19 Answers19

Linked

Related