如何在 SQL Server 中将 Unix 时间戳转换为日期/时间值

介绍在 SQL Server 中使用 DATEADD() 和 DATEDIFF() 将 Unix 时间戳(秒级、毫秒及纳秒级大时间戳)转换为标准 datetime 与 datetime2 日期时间值的实用技巧与溢出处理方法。

发布于

在 SQL Server 中,与外部应用(如 Node.js、Python、Go 或 REST API)进行数据交互时,经常会遇到 Unix 时间戳(Unix Timestamp,也称 Epoch 时间)。

Unix 时间戳是指自 1970 年 1 月 1 日 00:00:00 UTC(协调世界时) 起所经过的时间量。在 SQL Server 中,可以通过内置的时间计算函数将其快速转换为标准的 datetime 或高精度 datetime2 类型。


基础示例:秒级 Unix 时间戳转换

标准 Unix 时间戳以秒为单位。我们可以使用 DATEADD() 函数将秒数累加到基准日期 '1970-01-01' 上:

SELECT DATEADD(s, 1860935119, '1970-01-01') AS ConvertedDate;

输出:

ConvertedDate
-----------------------
2028-12-20 14:25:19.000

在此示例中,秒级时间戳 1860935119 被成功转换为 UTC 时间 2028-12-20 14:25:19.000。


逆向转换:获取当前时间的 Unix 时间戳

如果需要将当前系统时间转换为秒级 Unix 时间戳,可以使用 DATEDIFF() 计算当前 UTC 时间与 1970 年基准点之间的差值:

SELECT DATEDIFF(s, '1970-01-01', GETUTCDATE()) AS CurrentUnixTimestamp;

输出:

CurrentUnixTimestamp
--------------------
1732237200

进阶技巧:处理毫秒级时间戳(13 位时间戳)

在现代 Web 开发中,JavaScript 的 Date.now() 输出的是 13 位的毫秒级时间戳(例如 1732237200000)。

由于 DATEADD() 的第二个参数必须为 32 位整型(int,最大值为 2,147,483,647),直接传入毫秒数会导致算术溢出。处理毫秒时间戳的推荐做法是:将秒数与剩余毫秒数拆分累加:

DECLARE @ts_ms bigint = 1732237200123; -- 13 位毫秒时间戳

SELECT DATEADD(
    ms, 
    @ts_ms % 1000, 
    DATEADD(s, @ts_ms / 1000, '1970-01-01')
) AS ConvertedDateTime;

输出:

ConvertedDateTime
-----------------------
2024-11-22 01:00:00.123

应对高精度大时间戳(微秒/纳秒级溢出处理)

某些分布式系统或日志系统会生成 19 位的纳秒级时间戳。例如:

SELECT DATEADD(s, 1867914562715876900, '1970-01-01');

执行该语句会直接触发算术溢出错误:

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

这是因为 DATEADD() 的第二个参数无法容纳超出 int 范围的超大数值。针对这种高精度数值,有两种解决方案:

方案 A:截断精度至秒级

如果业务上不需要纳秒精度,可以使用 LEFT() 函数截取前 10 位秒级数值,并转换为 int:

DECLARE @ts bigint = 1867914562715876900;

SELECT DATEADD(s, CONVERT(int, LEFT(CAST(@ts AS varchar(30)), 10)), '1970-01-01') AS ConvertedDate;

输出:

ConvertedDate
-----------------------
2029-03-11 09:09:22.000

方案 B:使用 datetime2(7) 完整保留纳秒级精度

若必须完整保留纳秒精度,可以通过商(/ 1000000000)获取秒数,通过余数(% 1000000000)获取纳秒数,分别累加至 datetime2(7):

DECLARE @ts bigint = 1867914562715876900;

SELECT DATEADD(
    ns, 
    @ts % 1000000000, 
    DATEADD(s, @ts / 1000000000, CAST('1970-01-01' AS datetime2(7)))
) AS PreciseDateTime;

输出:

PreciseDateTime
---------------------------
2029-03-11 09:09:22.7158769

[!TIP] 上述计算结果均为 UTC 时间。如果需要将其转换为本地时区(例如中国标准时间 UTC+8),可以在 SQL Server 2016 及更高版本中使用 AT TIME ZONE 子句:

SELECT CAST(DATEADD(s, 1860935119, '1970-01-01') AS datetime2) 
       AT TIME ZONE 'UTC' 
       AT TIME ZONE 'China Standard Time' AS LocalTime;