Oracle: calculate difference between timestamps with time zones, taking daylight saving time (DST) i-CodePudding

On 27th of March 2022 02:00, in time zone 01:00 the clock goes one hour forward. So I would expect that based on the following code, v_date_diff would be 00000 03:00:00. However it is: 00000 04:00:00.

v_start_date          TIMESTAMP WITH TIME ZONE := TO_TIMESTAMP_TZ ('27/03/2022 00:59:59  1:00', 'DD/MM/YYYY HH24:MI:SS TZH:TZM');
v_end_date            TIMESTAMP WITH TIME ZONE := TO_TIMESTAMP_TZ ('27/03/2022 04:59:59  1:00', 'DD/MM/YYYY HH24:MI:SS TZH:TZM');
v_date_diff           INTERVAL DAY(5) TO SECOND(0) := v_end_date - v_start_date;

Why is this? How should I solve this?

CodePudding user response：

UTC does not follow Daylight Savings Time so any fixed offset from Daylight Savings Time would not follow Daylight Savings Time. So Oracle is correct that there are 4 hours between your timestamps.

If you specify a timestamp using a time zone that actually observes Daylight Savings Time, you'll get the result of 3 hours that you're looking for. For example, the time zone "Europe/Berlin" observes DST so the following code returns a difference of 3 hours

declare 
  v_start_date          TIMESTAMP WITH TIME ZONE := TO_TIMESTAMP_TZ ('27/03/2022 00:59:59 Europe/Berlin', 
                                                                     'DD/MM/YYYY HH24:MI:SS TZR');
  v_end_date            TIMESTAMP WITH TIME ZONE := TO_TIMESTAMP_TZ ('27/03/2022 04:59:59 Europe/Berlin', 
                                                                     'DD/MM/YYYY HH24:MI:SS TZR');
  v_date_diff           INTERVAL DAY(5) TO SECOND(0) := v_end_date - v_start_date;
begin
  dbms_output.put_line( v_date_diff );
end;