当前位置： 文江博客话题详情

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

发布于 2024-09-03 05:35:02 字数 56 浏览 3 评论 0原文

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

原文

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

如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论， 你可以免费 注册 一个本站的账号。

评论（20）

热血少△年 2024-09-10 05:35:02

这对我有用：

Select
    dateadd(S, [unixtime], '1970-01-01')
From [Table]

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

以下是维基百科的引用：

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

2038 年问题

此外，DateAdd 函数采用 int 来表示要添加的秒数。因此，如果您尝试添加超过 2147483647 秒，您将收到算术溢出错误。要解决此问题，您可以将添加分解为两次对 DateAdd 的调用，一次用于年份，一次用于剩余秒数。

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

这将允许您转换代表大于 2038 年的年份的时间戳。

This worked for me:

Select
    dateadd(S, [unixtime], '1970-01-01')
From [Table]

In case anyone 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 DateAdd 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.

回复 原文
镜花水月 2024-09-10 05:35:02

如果有人遇到以下错误：

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

因为 unix 时间戳是 bigint （而不是 int），您可以使用以下方法：

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

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

源：MSSQL bigint Unix 时间戳到日期时间（以毫秒为单位）

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

回复 原文
狂之美人 2024-09-10 05:35:02

尝试：

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

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
回复 原文
盛夏尉蓝 2024-09-10 05:35:02

像这样

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

现在就可以得到它（2010-05-25 07:56:23.000）

 SELECT dateadd(s,1274756183,'19700101 05:00:00:000')

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

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

回复 原文
别在捏我脸啦 2024-09-10 05:35:02

测试一下：

Sql 服务器：

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

MySql 服务器：

SELECT
  from_unixtime(timestamp) 
FROM 
  your_table

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

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

回复 原文
话少情深 2024-09-10 05:35:02

这将做到这一点：

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

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

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.

回复 原文
热风软妹 2024-09-10 05:35:02

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

在 SQL Server 2016 中，您可以使用 时区。您需要以 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)
UnixTimestampUTCDateLocalDate
15148080002018-01-01 12:00:00 +00:002018-01-01 04:00:00 -08:00
15278544002018- 06-01 12:00:00 +00:002018-06-01 05:00:00 -07:00

或者简单地说：

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)
UnixTimestampLocalDate
15148080002018-01-01 04:00:00 -08:00
15278544002018-06 -01 05:00:00 -07:00

注意：

  • 转换考虑了夏令时。太平洋时间 2018 年 1 月为 UTC-08:00，2018 年 6 月为 UTC-07:00。
  • 您可以通过将 DATETIMEOFFSET 转换为 DATETIME 来截取时区信息。
  • 有关 Windows 时区“名称”的完整列表，请探测 sys.time_zone_info 查看或使用tzutil。

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)
UnixTimestampUTCDateLocalDate
15148080002018-01-01 12:00:00 +00:002018-01-01 04:00:00 -08:00
15278544002018-06-01 12:00:00 +00:002018-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)
UnixTimestampLocalDate
15148080002018-01-01 04:00:00 -08:00
15278544002018-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.
回复 原文
莫言歌 2024-09-10 05:35:02

如果时间以毫秒为单位并且需要保存它们：

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

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'))
回复 原文
薯片软お妹 2024-09-10 05:35:02

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

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

有一些要点需要考虑：

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

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.
回复 原文
唔猫 2024-09-10 05:35:02

@DanielLittle 对具体问题有最简单、最优雅的答案。但是，如果您有兴趣转换为特定时区并考虑 DST（夏令时），则以下方法效果很好：

CAST(DATEADD(S, [UnixTimestamp], '1970-01-01') AT TIME ZONE 'UTC' AT TIME ZONE 'Pacific Standard Time' AS Datetime)

注意：此解决方案仅适用于 SQL Server 2016 及更高版本（和 Azure）。

要创建函数：

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

您可以像这样调用该函数：

SELECT   dbo.ConvertUnixTime([UnixTimestamp])
FROM     YourTable

@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
回复 原文
寻梦旅人 2024-09-10 05:35:02

这是基于 Daniel Little 对此问题所做的工作，但考虑到夏令时（由于 dateadd 函数的 int 限制，适用于日期 01-01 以及更大的日期）：

我们首先需要创建一个表来存储夏令时的日期范围（来源：美国时间的历史）：

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

现在我们为每个美国时区创建一个函数。这是假设 unix 时间以毫秒为单位。如果以秒为单位，请从代码中删除 /1000：

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

亚利桑那

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

阿拉斯加

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

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
回复 原文
清风疏影 2024-09-10 05:35:02
//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

//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
回复 原文
蓝天白云 2024-09-10 05:35:02

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

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

For GMT, here is the easiest way:

Select dateadd(s, @UnixTime+DATEDIFF (S, GETUTCDATE(), GETDATE()), '1970-01-01')
回复 原文
无远思近则忧 2024-09-10 05:35:02
CREATE FUNCTION dbo.ConvertUnixToDateTime(@Datetime BIGINT)
RETURNS DATETIME
AS
BEGIN 
    RETURN (SELECT DATEADD(second,@Datetime, CAST('1970-01-01' AS datetime)))
END;
GO

CREATE FUNCTION dbo.ConvertUnixToDateTime(@Datetime BIGINT)
RETURNS DATETIME
AS
BEGIN 
    RETURN (SELECT DATEADD(second,@Datetime, CAST('1970-01-01' AS datetime)))
END;
GO
回复 原文
谈下烟灰 2024-09-10 05:35:02

更好的？该函数将以毫秒为单位的 unixtime 转换为 datetime。它损失了几毫秒，但对于过滤仍然非常有用。

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

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
回复 原文
浮云落日 2024-09-10 05:35:02

解决方案可以如下：

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

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'));
回复 原文
尤怨 2024-09-10 05:35:02

这是我使用的函数，它通过执行两步 dateadd() 来处理大于 2038 的日期。它返回 UTC，但请参阅例如 Kenny 的回答了解时区和 DST 处理。

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;

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;
回复 原文
请你别敷衍 2024-09-10 05:35:02

在 SQL Server 上你可以尝试这个：

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

示例：

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

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

回复 原文
阳光①夏 2024-09-10 05:35:02

@salman，@daniel-little

我认为丹尼尔的答案是错误的。

他的逻辑仅使用闰年的秒数进行计算。所以转换后的值不正确。

我认为我们需要如下函数：

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
;

我使用组件验证了边缘情况，如下所示：

  • 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);
  • 查询
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;
  • 结果集
|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|

我的函数返回的值全部匹配源日期时间，但是，丹尼尔的逻辑返回的值并不全部匹配源日期时间。

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

回复 原文
最冷一天 2024-09-10 05:35:02
CAST(DATEADD(S, UnixTimestamp, '1970-01-01') AT TIME ZONE 'UTC' AT TIME ZONE 'Central Europe Standard Time' AS Datetime)

结合sys.time_zone_info，您可以检查您的本地时区。

CAST(DATEADD(S, UnixTimestamp, '1970-01-01') AT TIME ZONE 'UTC' AT TIME ZONE 'Central Europe Standard Time' AS Datetime)

In combination with sys.time_zone_info, you can check your local timezone.

回复 原文
~没有更多了~

关于作者

素罗衫

暂无简介

0 文章
0 评论
20 人气
发私信

相关话题

更多

热门标签

操作系统 程序设计 IT运维 Linux系统管理 JavaScript 服务器应用 solaris C/C++ PHP Shell BSD Vue.js aix Oracle Python HTML 系统管理 HTML5 CSS 前端
更多

推荐作者

不再见

文章 0 评论 0

真是无聊啊

文章 0 评论 0

樱娆

文章 0 评论 0

浅语花开

文章 0 评论 0

烛光

文章 0 评论 0

绻影浮沉

文章 0 评论 0

更多

友情链接

文江博客
    我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站，即表示您同意使用 Cookies 和您的相关数据。
    原文