我有一个混乱的表格,里面装满了事件,有时会得到DateTime,而在其他地方,会给Date字段分配DateTimeOffset,类似于此(如果你住在Divided Kingdom的经度之外足够远的地方(:
DECLARE @MY_LOG TABLE ([MOMENT] DATETIMEOFFSET, [PAYLOAD] NVARCHAR(200));
INSERT INTO @MY_LOG ([MOMENT],[PAYLOAD]) VALUES (GETDATE(),'first entry')
WAITFOR DELAY '00:00:00.100';
INSERT INTO @MY_LOG ([MOMENT],[PAYLOAD]) VALUES (SYSDATETIMEOFFSET(),'second entry')
WAITFOR DELAY '00:00:00.100';
INSERT INTO @MY_LOG ([MOMENT],[PAYLOAD]) VALUES (GETDATE(),'third entry')
WAITFOR DELAY '00:00:00.100';
INSERT INTO @MY_LOG ([MOMENT],[PAYLOAD]) VALUES (SYSDATETIMEOFFSET(),'forth entry')
SELECT [MOMENT],[PAYLOAD] FROM @MY_LOG ORDER BY [MOMENT] ASC;
最后一次选择的结果是这个例子:
2020-03-31 02:39:10.6779279 +02:00 second entry
2020-03-31 02:39:10.8809259 +02:00 forth entry
2020-03-31 02:39:10.5730000 +00:00 first entry
2020-03-31 02:39:10.7770000 +00:00 third entry
我试图通过使用SWITCH offset来修复它,它添加了丢失的偏移,但也补偿了它:
SELECT [MOMENT],
[PAYLOAD],
CASE WHEN DATEPART(tz,[MOMENT])=0 THEN SWITCHOFFSET(MOMENT, '+02:00') ELSE MOMENT END AS FIXED
FROM @MY_LOG ORDER BY FIXED ASC;
结果是:
2020-03-31 02:39:10.6779279 +02:00 second entry 2020-03-31 02:39:10.6779279 +02:00
2020-03-31 02:39:10.8809259 +02:00 forth entry 2020-03-31 02:39:10.8809259 +02:00
2020-03-31 02:39:10.5730000 +00:00 first entry 2020-03-31 04:39:10.5730000 +02:00
2020-03-31 02:39:10.7770000 +00:00 third entry 2020-03-31 04:39:10.7770000 +02:00
我可能还需要考虑像这个问题中那样的切换时刻,但目前我正在寻找一种非破坏性(只读(解决方案来按日期排序
在发布前找到答案:通过转换为datetime2(7)
:来剥离偏移
SELECT [MOMENT],
[PAYLOAD],
CONVERT(datetime2(7),[MOMENT]) AS FIXED
FROM @MY_LOG ORDER BY FIXED ASC;
结果是一个正确排序的表(除了夏令时切换时每年两个小时(。
2020-03-31 03:04:04.3630000 +00:00 first entry 2020-03-31 03:04:04.3630000
2020-03-31 03:04:04.4689375 +02:00 second entry 2020-03-31 03:04:04.4689375
2020-03-31 03:04:04.5670000 +00:00 third entry 2020-03-31 03:04:04.5670000
2020-03-31 03:04:04.6709404 +02:00 forth entry 2020-03-31 03:04:04.6709404