How can I convert UNIX timestamp (bigint) to DateTime in SQL Server?
This question is related to
sql
sql-server
tsql
datetime
unix-timestamp
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
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'))
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:
@ticksofday
and the first line of the algorithm accordingly.1900-01-01
is the origin date for datetime2
, just as is the epoch 1970-01-01
for unix timestamps.float
s 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
. 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.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.
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'));
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
@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
For GMT, here is the easiest way:
Select dateadd(s, @UnixTime+DATEDIFF (S, GETUTCDATE(), GETDATE()), '1970-01-01')
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
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
CREATE FUNCTION dbo.ConvertUnixToDateTime(@Datetime BIGINT)
RETURNS DATETIME
AS
BEGIN
RETURN (SELECT DATEADD(second,@Datetime, CAST('1970-01-01' AS datetime)))
END;
GO
//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
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:
DATETIMEOFFSET
to DATETIME
.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
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
Source: Stackoverflow.com