Oracle: calculate difference between timestamps with time zones, taking daylight saving time (DST) into account

user_11403673 0
Viewed 143

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?

timestamp-with-timezone dst oracle
2 Answers

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;
Justin_Cave_103970

Oracle Database performs all timestamp arithmetic in UTC time. +1:00 means 1 hour before UTC - always, i.e. it does not apply any Daylight-Saving-Times. Take for example Algeria, which has also time zone +1:00 but does not use Daylight-Saving-Times.

When you say "On 27th of March 2022 02:00, in time zone +01:00 the clock goes one hour forward." then your data is wrong. In this case it must be (using TIMESTAMP literals)

v_end_date   TIMESTAMP WITH TIME ZONE := TIMESTAMP '2022-03-27 04:59:59 +2:00';

Then the difference will be 3 hours. Or use time zone region name (e.g. Europe/Berlin) as suggested by Justin Cave.

user_30272660
Related