如何在 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;