UTC time zone calculation-CodePudding

We have data stored in our timezone ('AUS Eastern Standard Time') into SQL Server database, and we have to do calculations. Is it possible to convert both dates to UTC for calculations, so that it calculates the right difference?

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

CodePudding user response：

Datetime values will be problematic without an offset as @Larnu suggest in comments. This will occur when clocks are set back and result in overlapping times before and after the time change. You'll need rules to determine the offset in that case.

In your example, where clocks are set forward, there should be no overlap or ambiguity. This allows one to use AT DATE TIME AUS Eastern Standard Time') to convert the datetime values to datetimeoffset for the duration calculation. If an invalid datetime value is stored (within the gap when clocks should have been change forward), AT DATE TIME will return the offset after the clock change.

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 DATEADD(second
        , DATEDIFF(second, @startdate AT TIME ZONE 'AUS Eastern Standard Time'
        , @enddate AT TIME ZONE 'AUS Eastern Standard Time')
    , CAST('00:00:00' AS time(0)));

So you'll have one hour of the year you'll need to develop business rules for. AT DATE TIME will return the offset before the change during the ambiguous interval when clocks are set backwards.

CodePudding user response：

DATETIME is a not recommended because it's a datatype with plenty of erronous features and it is not accurate.

Use DATETIME2 instaed (MS recommendation)

Then SET the TIMEZONE with the operator AT TIME ZONE :

Use ALSO DATETDIFF to get a difference because a minus sign must only apply to numbers not for date/time calculus...

Finally :

DECLARE @startdate DateTime2 = '2022-10-01 23:13:00.000'; 
DECLARE @enddate DateTime2 = '2022-10-02 12:08:00.000'; 
DECLARE @difsecond INT = DATEDIFF(s, @startdate AT TIME ZONE 'AUS Eastern Standard Time', @enddate AT TIME ZONE 'AUS Eastern Standard Time')  
SELECT CAST(DATEADD(s, @difsecond, CAST('2000-01-01' AS DATETIME2(0))) AS TIME)