如何将Unix毫秒时间戳从GMT+0转GMT+2并处理夏令时？

问题说明 #

数据库存储的是Unix毫秒级时间戳，现有SQL转换结果为GMT+0（UTC）时间，需要转换为GMT+2/CEST时区，同时解决夏令时导致的时间不准确问题，此前尝试的两段SQL代码未达成需求。

此前尝试的无效代码 #

第一段：

SELECT TOP(100) [unixcolumn], CAST(DATEADD(ms, CAST(RIGHT([unixcolumn],3) AS SMALLINT), 
DATEADD(s, [unixcolumn] / 1000, '1970-01-01')) AS DATETIME2(3))
FROM [db].[dbo].[table]

第二段：

SELECT DATEADD(s, LEFT([unixcolumn], LEN([unixcolumn]) - 3), '1970-01-01')
FROM [db].[dbo].[table]

解决方案 #

使用SQL Server的AT TIME ZONE函数可以自动处理时区转换及夏令时规则，以下是正确的转换代码：

SELECT TOP(100) 
    [unixcolumn],
    -- 转换为UTC时间（GMT+0）
    CAST(DATEADD(ms, [unixcolumn] % 1000, DATEADD(s, [unixcolumn] / 1000, '1970-01-01')) AS DATETIME2(3)) AS UTC_Time,
    -- 转换为CEST/GMT+2时区（自动识别夏令时）
    CAST(DATEADD(ms, [unixcolumn] % 1000, DATEADD(s, [unixcolumn] / 1000, '1970-01-01')) AS DATETIME2(3)) 
    AT TIME ZONE 'UTC' 
    AT TIME ZONE 'Central European Standard Time' AS CEST_Time
FROM [db].[dbo].[table]

代码说明 #

1. UTC时间转换：通过DATEADD先将毫秒时间戳转换为秒级UTC时间，再补充剩余毫秒数，得到精确到毫秒的UTC时间。
2. 时区转换与夏令时处理：AT TIME ZONE 'UTC'标记当前时间为UTC时区，再通过AT TIME ZONE 'Central European Standard Time'转换为中欧时区——该时区会自动根据日期切换CET（冬令时，GMT+1）和CEST（夏令时，GMT+2），无需手动调整。

此前代码的问题 #

• 第一段代码仅完成了UTC时间转换，未进行时区偏移，结果仍为GMT+0时间。
• 第二段代码使用LEFT截取时间戳的方式存在风险（若时间戳长度不固定会出错），且未保留毫秒精度，同样未处理时区转换。

内容的提问来源于stack exchange，提问作者Stefan Meeuwessen