Serilog日志表TimeStamp带时区 被SQL Server转换为smalldatetime时忽略如何处理

问题原因 #

smalldatetime 类型本身不携带时区信息，直接使用 CAST 将 datetimeoffset 类型的 TimeStamp 字段转为 smalldatetime 时，SQL Server 会直接丢弃时区偏移部分，只保留原时间的数值部分，不会做任何时区转换，所以才会出现UTC时间（+00:00）没有调整到本地时区的问题。你观察到 -04:00 时区的时间转换后结果正确，只是因为该时间的数值本身已经是你所在时区的本地时间，刚好匹配需求。

正确处理方式 #

方案1：固定时区偏移 #

如果你所在时区没有夏令时，偏移量固定，可以用 SWITCHOFFSET 函数先将时间调整到目标时区，再做类型转换：

SELECT TOP(20) 
    Id, TimeStamp, 
    CAST(SWITCHOFFSET(TimeStamp, '-04:00') AS smalldatetime) AS TimeStampx 
FROM 
    SerilogLogs 
WHERE
    Level = 'Error' 
ORDER BY
    Id DESC;

以你给出的示例时间 2021-09-16 13:05:00.3432122 +00:00 为例，经过 SWITCHOFFSET 调整后会转为 2021-09-16 09:05:00.3432122 -04:00，再转换为 smalldatetime 就能得到正确的本地时间。

方案2：自动适配夏令时 #

如果需要自动处理夏令时调整，可以使用 AT TIME ZONE 语法，配合系统内置的时区标识实现自动转换：

SELECT TOP(20) 
    Id, TimeStamp, 
    CAST(TimeStamp AT TIME ZONE 'UTC' AT TIME ZONE 'Eastern Standard Time' AS smalldatetime) AS TimeStampx 
FROM 
    SerilogLogs 
WHERE
    Level = 'Error' 
ORDER BY
    Id DESC;

你可以执行 SELECT name FROM sys.time_zone_info 查询所有支持的时区名称，替换为你所在地区对应的时区即可。

内容的提问来源于stack exchange，提问作者Ted Scheckler