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?

Solution 1

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.

Solution 2

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

Solution 3

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

Solution 4

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

Solution 5

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

View more solutions

Comments

[Andomar]

+1 For the UTC->local conversion. Note that the summer/wintertime will still be off if you try to translate June 10th during February.

[Pavel Horal]

-1 for the UTC->local conversion. It is not correctly handling daylight saving time. For me, this is misguiding.

[Seth]

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

[BenDundee]

This should be marked as correct. I wish I could upvote this answer again every time I land here :)

[Ross Brasseaux]

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

[gustavohenke]

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

[Nightking]

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

[Svisstack]

why 05:00:00 instead of 00:00:00?

[Jordy van Eijk]

@Svisstack the 5 hours is for timezone difference. 5:00:00 means he's GMT-5 hours

[Nathan]

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). en.wikipedia.org/wiki/Year_2038_problem

[Patrick H]

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." docs.microsoft.com/en-us/sql/t-sql/functions/… en.wikipedia.org/wiki/Year_2038_problem

[clifton_h]

Works like a charm. If you need to adjust for the timezone, then certainly do that but consider this very efficient

[Danish]

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.

[Salman A]

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.

[Vespucci75fr]

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: stackoverflow.com/a/53177954/687490

[access_granted]

Also was getting an overflow, normally meaning that milliseconds are involved, solved simply as: select dbo.fn_ConvertToDateTime( src_column/1000 ) from src_table;

[Adam]

this causes Arithmetic overflow due to timestamps that no longer "fit" into an int

[colm.anseo]

@Danish your time of 1517270400000 is epoch milli-seconds. Divide by 1000 and it should work.

[colm.anseo]

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'

[Muhammad Saqib]

Msg 8115, Level 16, State 2, Line 36 Arithmetic overflow error converting expression to data type int.

[BWFC]

@Nathan SELECT DATEADD(SECOND,2147483648%60,DATEADD(MI,2147483648/60,'1970-‌​01-01 00:00:00.000'))

[G DeMasters]

Given epoch milliseconds, even better: SELECT DATEADD(ms, 1517270454852%1000, DATEADD(S, 1517270454852/1000, '1970-01-01'))

[john]

thank you . this should be upvoted 1462924862735870900 TIMES!!!!!!!!!!!!!!!!!!!!!

[julianm]

Glad to help @John

[TiyebM]

I am getting Conversion failed when converting the varchar value '1.20787e+0' to data type int

[julianm]

Your number is in scientific notation @TiyebM. And it's not a integer value (it has decimals)

[luisdev]

How does this accommodate other UTC timezones? E.g. UTC+2 or UTC+5?

[luisdev]

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?

[Sau001]

Did not work with me with this value 1624279488665004 . The error was Arithmetic overflow error converting expression to data type int.

[specimen]

Finally an answer that takes time zones into consideration! A list of time zones in SQL server can easily be found here: database.guide/…

[Daniel Little]

Thanks for the feedback, I've now addressed the 2038 problem