Sometimes, you get simple questions but the most obvious answer is wrong. Yesterday, my colleague Dion had one for us;

How many hours does one day have?

Nope, 24 is not the correct answer. Well, at least in my timezone… We have something delightful that’s called “Daylight Savings”. So, one day a year has 23 hours and another has 25.

So, how do we know – using PL/SQL- how many hours a day has?

UTC

We figured our problem could be solved by converting our date to the UTC timezone. UTC doesn’t have Daylight Savings, so if we convert our date to UTC, and then calculate the hours between (UTC ALWAYS has 24 hours luckily) we get the correct hours.

And if we sent a fake date & hour (i.e. with daylight savings, 2 am turns to 3 am) we ran into an ORA-01878 – Specified field not found in datetime. So, we just added a small bonus so this wouldn’t affect our function if someone decided to put in some wrong date and time, cause you can’t always now when Daylight savings is happening.

So, when the ORA-01878 is happening, we catch it in an EXCEPTION handler, take one day earlier, cast it to UTC and added the day we removed before casting.

Code

set serveroutput on
clear screen

DECLARE 
  l_daylightstarts DATE := to_date('27/03/2022', 'DD/MM/YYYY' );
  l_daylightends DATE := to_date('30/10/2022', 'DD/MM/YYYY' );
  l_regularday DATE := to_date('18/03/2022', 'DD/MM/YYYY' );
  
  l_faketime DATE := to_date('27/03/2022 02:00', 'DD/MM/YYYY HH24:MI' );

  --Convert local time to UTC.
  FUNCTION fnc_local_to_utc(p_local IN DATE) RETURN DATE IS
  BEGIN
    return CAST((FROM_TZ(p_local, 'Europe/Berlin') AT TIME ZONE 'UTC') AS DATE);
  EXCEPTION 
    --Needed for date times that don't exist in timezone
    WHEN OTHERS THEN
      return CAST((FROM_TZ(p_local - 1, 'Europe/Berlin') AT TIME ZONE 'UTC') AS DATE)  +1;
  END;
  
  --Calculate hours of day
  PROCEDURE prc_hours_of_day(p_day IN DATE) IS
  BEGIN
    dbms_output.put_line( p_day
                         || ' has: ' 
                         || round((fnc_local_to_utc(p_day + 1) - fnc_local_to_utc( p_day ))*24)
                         || ' hours. ');
  END;
 
BEGIN
  prc_hours_of_day(l_daylightstarts);
  prc_hours_of_day(l_daylightends);
  prc_hours_of_day(l_regularday);
  
  --Just to be sure it also works for times that don't exist.
  prc_hours_of_day(l_faketime);
end;
/

---------------------------------
27-03-2022 00:00:00 has: 23 hours. 
30-10-2022 00:00:00 has: 25 hours. 
18-03-2022 00:00:00 has: 24 hours. 
27-03-2022 02:00:00 has: 23 hours.

Related Posts