根据时区和偏移量计算时差

Question

我们在接近实时的数据仓库中将数据从Oracle SQL传输到SQL Server。(Oracle是事务性数据库)对于我们使用的数据，时间每年变化两次。我们在计算DateTime差异时一直有问题，我必须在短期和长期解决方案上工作，我需要建议。

1. 新数据传入:我可以从头开始创建表，并为所有时间变量使用datetimeoffset数据字段。这显示日期在SQL Server查询在当地时区，并保存在他们的实际UTC时间?当我计算差值，它能给出正确的差值吗?

2. 没有偏移信息的现有数据:我已经将数据转换为当前时区。我们有两个时区AEST和AEDT。当我们计算kpi的时间差时，它给出了不正确的时间。有什么办法可以解决吗?创建一个将日期和时间转换为UTC并减去差异并返回值的函数可以工作吗?

以下示例:

DECLARE @startdate DateTime = '2022-10-01 23:13:00.000'; --UTC 2022-10-01 13:13:00.00
DECLARE @enddate DateTime = '2022-10-02 12:08:00.000' --UTC 2022-10-02 01:08:00.00
select CAST((@enddate - @startdate) as time(0)) 'Difference'
Time difference: 12:55:00
Actual Time Difference: 11:55:00

Answer 1

对于您的示例，如果您在数据中包含时区信息，则日期计算结果为

DECLARE @startdate datetimeoffset(0) = '2022-10-01 23:13:00 +10:00'; --UTC 2022-10-01 13:13:00.00
DECLARE @enddate datetimeoffset(0) = '2022-10-02 12:08:00 +11:00' --UTC 2022-10-02 01:08:00.00
declare @minutes int =  datediff(minute, @startdate, @enddate);
select @startdate at time zone 'utc', 
@enddate at time zone 'utc';
select [hours] = @minutes / 60,
[minutes] = @minutes - 60 * (@minutes / 60)

要处理现有的数据，您可以通过使用适当的时区名称运行at time zone来对datetimeoffset进行一次转换。在上面的例子中，我使用了UTC，但是你可以在sys.time_zone_info中找到所有支持的时区。

关于你关于内部存储格式和显示的第一个问题，我不确定它是否在内部使用UTC。至于显示，默认情况下，它将使用存储它们的时区输出它们。也就是说，如果以+10:00作为偏移量存储给定值，那么它将以这种方式显示。您可以通过上面已经描述的方法或使用switchoffset()进行转换。最后，如果需要的话，您可以使用datepart获取偏移量，并注意它以分钟数返回偏移量(与需要返回int的datepart一致)。