如何在 SQL Server 中将 bigint（UNIX 时间戳）转换为日期时间？

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

提问by salman

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

如何在 SQL Server 中将 UNIX 时间戳 (bigint) 转换为 DateTime？

采纳答案by KM.

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

回答by Daniel Little

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.

如果有人想知道为什么 1970-01-01，这被称为Epoch 时间。

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.

自 1970 年 1 月 1 日星期四 00:00:00 协调世界时 (UTC) 起经过的秒数，[1][注 1] 不计算闰秒。

回答by jmojico

If anyone getting below error:

如果有人收到以下错误：

Arithmetic overflow error converting expression to data type int

将表达式转换为数据类型 int 时出现算术溢出错误

due to unix timestamp is in bigint (instead of int), you can use this:

由于unix时间戳在bigint（而不是int）中，您可以使用：

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

Replace the hardcoded timestamp for your actual column with unix-timestamp

用 unix-timestamp 替换实际列的硬编码时间戳

Source: MSSQL bigint Unix Timestamp to Datetime with milliseconds

来源：MSSQL bigint Unix Timestamp to Datetime with milliseconds

回答by SQLMenace

Like this

像这样

add the Unix (epoch) datetime to the base date in seconds

以秒为单位将 Unix（纪元）日期时间添加到基准日期

this will get it for now (2010-05-25 07:56:23.000)

这将得到它现在 (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

如果你想反向，看看这个http://wiki.lessthandot.com/index.php/Epoch_Date

回答by Ovidiu Pacurar

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.

而不是！精确！根据时间戳的精度使用：ss、ms 或 mcs。Bigint 能够保持微秒级精度。

回答by Mohamad Hamouday

Test this:

测试这个：

Sql server:

数据库服务器：

SELECT dateadd(S, timestamp, '1970-01-01 00:00:00') 
     FROM 
your_table

MySql server:

MySql服务器：

SELECT
  from_unixtime(timestamp) 
FROM 
  your_table

http://www.w3resource.com/mysql/date-and-time-functions/mysql-from_unixtime-function.php

http://www.w3resource.com/mysql/date-and-time-functions/mysql-from_unixtime-function.php

回答by Salman A

Adding nseconds to 1970-01-01will give you a UTC datebecause n, the Unix timestamp, is the number of seconds that have elapsed since 00:00:00 Coordinated Universal Time (UTC), Thursday, 1 January 1970.

添加n秒 to1970-01-01将为您提供UTC 日期，因为n是 Unix 时间戳，是自 00:00:00 协调世界时 (UTC), 1970 年 1 月 1 日星期四以来经过的秒数。

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:

在 SQL Server 2016 中，您可以使用AT TIME ZONE. 您只需要知道 Windows 标准格式的时区名称：

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

• 您可以通过强制转换DATETIMEOFFSET来切断时区信息DATETIME。
• 转换考虑了夏令时。太平洋时间是 2018 年 1 月的 UTC-08:00 和 2018 年 6 月的 UTC-07:00。

回答by user3450075

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

回答by Chris Tophski

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.

我也不得不面对这个问题。不幸的是，没有一个答案（这里和其他几十页）让我满意，因为由于某处的 32 位整数转换，我仍然无法达到 2038 年之后的日期。

A solution that did work for me in the end was to use floatvariables, so I could have at least a max date of 2262-04-11T23:47:16.854775849. Still, this doesn't cover the entire datetimedomain, but it is sufficient for my needs and may help others encountering the same problem.

最后对我有用的解决方案是使用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 |
-- +-----------------------------+-------------------+-----------------------------+

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 @ticksofdayand 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-01is the origin date for datetime2, just as is the epoch 1970-01-01for 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 datetime2allowed, but it is allowed to cast numerics to datetimeexplicitly 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 datetime2will be allowed or the explicit cast to datetimewill be disallowed, so this may either break or there may come an easier way some day.

• 100ns 精度是我的要求，但这似乎是 64 位 unix 时间戳的标准分辨率。如果您使用任何其他分辨率，则必须相应地调整@ticksofday算法的第一行。
• 我正在使用其他存在时区等问题的系统，我发现对我来说最好的解决方案是始终使用 UTC。根据您的需要，这可能会有所不同。
• 1900-01-01是 的起始日期datetime2，就像1970-01-01unix 时间戳的纪元一样。
• floats 帮助我解决了 2038 年问题和整数溢出等问题，但请记住，浮点数的性能不是很好，并且可能会减慢处理大量时间戳的速度。此外，浮点数可能会因舍入误差而导致精度损失，正如您在上面最大日期的示例结果的比较中所见（此处，误差约为 1.4425 毫秒）。
• 在算法的最后一行中，有一个强制转换为datetime。不幸的是，没有从数值到datetime2allowed 的显式转换，但允许将数字datetime显式转换为 ，而这又隐式转换为datetime2。目前这可能是正确的，但在 SQL Server 的未来版本中可能会发生变化：要么会有一个dateadd_big()函数，要么datetime2允许显式强制转换datetime为，要么不允许显式强制转换为，因此这可能会中断或可能会出现有一天更简单的方法。

回答by Saolin

For GMT, here is the easiest way:

对于 GMT，这是最简单的方法：

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