European Summer Time

I needed a function to determine how many hours fit in a given day. In the Netherlands, a function that does "return 24;" is not good enough. In the northern hemisphere, Daylight Saving Time is quite common. And in Europe, except Iceland, we have European Summer Time which states that the clock moves one hour forward on the last Sunday in March and it moves one hour backwards on the last Sunday in October. Which means there is a day in March that consists of 23 hours and in October there is one with 25 hours.

So, for my function I needed two other functions: one that returns the date where European Summer Time starts and one that returns the date where European Summer Time ends. I found existing code which - if it had been modularized - looked like this:

create function european_summer_time_start1 (p_year in number)
  return date
is
begin
  execute immediate
    'alter session set nls_date_language = "English"'
  ;
  return
    next_day(to_date('01-04-'||p_year,'dd-mm-rrrr')-1, 'sunday') - 7
  ;
end european_summer_time_start1;

and

create function european_summer_time_end1 (p_year in number)
  return date
is
begin
  execute immediate
    'alter session set nls_date_language="English"'
  ;
  return
    next_day(to_date('01-11-'||p_year,'dd-mm-rrrr')-1, 'sunday') - 7
  ;
end european_summer_time_end1;

It uses the NEXT_DAY function to determine the next sunday after March 31 and October 31, and then subtracts a week to get the last Sunday of March and October. The second parameter ('sunday') is in the date language of the session. And to be sure the function runs correctly, the nls_date_language parameter is set. I don't like this at all, because it sets a session parameter without setting the value back after the function has finished. Code might run differently depending on whether the function has been run previously in the session or not. I want the code to be NLS independent and I came up with these two functions:

create function european_summer_time_start2 (p_year in number)
  return date
is
begin
  return
    trunc
    ( to_date(to_char(p_year) || '0401','yyyymmdd')
    , 'iw'
    ) - interval '1' day
  ;
end european_summer_time_start2;

and

create function european_summer_time_end2 (p_year in number)
  return date
is
begin
  return
    trunc
    ( to_date(to_char(p_year) || '1101','yyyymmdd')
    , 'iw'
    ) - interval '1' day
  ;
end european_summer_time_end2;

Here I use the TRUNC-function to set the date value back to the beginning of the ISO-week, which is always on Monday, regardless of NLS-settings. Then subtract one day and we have the last Sunday of March/October.

Then I saw the formula on Wikipedia in the same link I mentioned earlier:

Formula used to calculate the beginning of European Summer Time:
Sunday (31 − (5 * y ÷ 4 + 4) mod 7) March at 01:00 GMT

Formula used to calculate the end of European Summer Time:
Sunday (31 − (5 * y ÷ 4 + 1) mod 7) October at 01:00 GMT

Implementing these formulas lead to a third variant of the two functions:

create function european_summer_time_start3 (p_year in number)
  return date
is
begin
  return
    to_date
    ( to_char(p_year) ||
      '03' ||
      to_char(31 - trunc(mod(5 * p_year / 4 + 4, 7)))
    , 'yyyymmdd'
    )
  ;
end european_summer_time_start3;

and

create function european_summer_time_end3 (p_year in number)
  return date
is
begin
  return
    to_date
    ( to_char(p_year) ||
      '10' ||
      to_char(31 - trunc(mod(5 * p_year / 4 + 1, 7)))
    , 'yyyymmdd'
    )
  ;
end european_summer_time_end3;

Next, I compared the three variants. They return the right dates:

rwijk@TEST10> select european_summer_time_start1(year) ws1
  2       , european_summer_time_start2(year) ws2
  3       , european_summer_time_start3(year) ws3
  4       , european_summer_time_end1(year)   sw1
  5       , european_summer_time_end2(year)   sw2
  6       , european_summer_time_end3(year)   sw3
  7    from ( select 1999 + level year
  8             from dual
  9          connect by level <= 11
 10         )
 11  /

WS1        WS2        WS3        SW1        SW2        SW3
---------- ---------- ---------- ---------- ---------- ----------
26-03-2000 26-03-2000 26-03-2000 29-10-2000 29-10-2000 29-10-2000
25-03-2001 25-03-2001 25-03-2001 28-10-2001 28-10-2001 28-10-2001
31-03-2002 31-03-2002 31-03-2002 27-10-2002 27-10-2002 27-10-2002
30-03-2003 30-03-2003 30-03-2003 26-10-2003 26-10-2003 26-10-2003
28-03-2004 28-03-2004 28-03-2004 31-10-2004 31-10-2004 31-10-2004
27-03-2005 27-03-2005 27-03-2005 30-10-2005 30-10-2005 30-10-2005
26-03-2006 26-03-2006 26-03-2006 29-10-2006 29-10-2006 29-10-2006
25-03-2007 25-03-2007 25-03-2007 28-10-2007 28-10-2007 28-10-2007
30-03-2008 30-03-2008 30-03-2008 26-10-2008 26-10-2008 26-10-2008
29-03-2009 29-03-2009 29-03-2009 25-10-2009 25-10-2009 25-10-2009
28-03-2010 28-03-2010 28-03-2010 31-10-2010 31-10-2010 31-10-2010

11 rows selected.

But as mentioned in the Wikipedia article, the third variant produces wrong results in 2100, which is not a leap year:

rwijk@TEST10> select european_summer_time_start1(year) ws1
  2       , european_summer_time_start2(year) ws2
  3       , european_summer_time_start3(year) ws3
  4       , european_summer_time_end1(year)   sw1
  5       , european_summer_time_end2(year)   sw2
  6       , european_summer_time_end3(year)   sw3
  7    from ( select 2097 + level year
  8             from dual
  9          connect by level <= 5
 10         )
 11  /

WS1        WS2        WS3        SW1        SW2        SW3
---------- ---------- ---------- ---------- ---------- ----------
30-03-2098 30-03-2098 30-03-2098 26-10-2098 26-10-2098 26-10-2098
29-03-2099 29-03-2099 29-03-2099 25-10-2099 25-10-2099 25-10-2099
28-03-2100 28-03-2100 27-03-2100 31-10-2100 31-10-2100 30-10-2100
27-03-2101 27-03-2101 26-03-2101 30-10-2101 30-10-2101 29-10-2101
26-03-2102 26-03-2102 25-03-2102 29-10-2102 29-10-2102 28-10-2102

5 rows selected.

It probably is the faster function though, as it doesn't need date arithmetic and ISO week calculation, but I was curious in how much it differs. Here is the test that I used:

rwijk@TEST10> var N number
rwijk@TEST10> exec :N := 100000

PL/SQL procedure successfully completed.

rwijk@TEST10> set timing on
rwijk@TEST10> declare
  2    l_date date;
  3  begin
  4    for i in 1..:N
  5    loop
  6      l_date := european_summer_time_start1(2010);
  7    end loop;
  8  end;
  9  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:04.20
rwijk@TEST10> declare
  2    l_date date;
  3  begin
  4    for i in 1..:N
  5    loop
  6      l_date := european_summer_time_start2(2010);
  7    end loop;
  8  end;
  9  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:01.30
rwijk@TEST10> declare
  2    l_date date;
  3  begin
  4    for i in 1..:N
  5    loop
  6      l_date := european_summer_time_start3(2010);
  7    end loop;
  8  end;
  9  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.79

The test was executed several times with consistent timings. So now I have to decide whether I use the function that works fastest but breaks in 2100, or one that runs a little slower but keeps on working. Performance doesn't really matter here, so I'll stick with the second variant.

The PL/SQL Challenge effect

In Google Analytics I noticed a strange peak in my page visits. On a normal weekday when I haven't posted something new, approximately 200 people visit one or more blogposts here. But on Thursday July 8, there were 299. When zooming in on that day, I noticed that my blogpost about SAVE EXCEPTIONS was visited 101 times, where 5 or 10 per day is normal for that blogpost. When something like this happens, it is usually caused by someone posting a link to my blog, but that was not the case now. Then I looked at the searched keywords and I saw these lines (I filtered out lots of other rows):

save exceptions 8
oracle save exceptions 6
forall log errors save exceptions 2
forall save exceptions log errors 2
forall save exceptions oracle 2
log errors save exceptions 2
oracle forall save exceptions 2
save exception 2
"log errors" "save exceptions" 1
"log errors" "save exceptions" "dbms_errlog.create_error_log" 1
"log errors" "save exceptions" forall 1
"save exceptions" "log errors" 1
_http://rwijk.blogspot.com/2007/11/save-exceptions.html 1
both log errors and save exception 1
create_error_log "save exceptions" 1
forall 'log error' 'save exceptions' 1
forall log errors save exception both togheter 1
forall save exception 1
forall save exceptions 1
forall save exceptions and log errors together 1
forall save exceptions log errors sql%bulk_exceptions 1
forall statement "log errors" "save exceptions" "bulk_exceptions" 1
log eroors save exceptions forall 1
log error oracle save exceptions 1
log error save exceptions 1
log errors save exceptions forall oracle 1
log errors,save exception with for all oracle 1
oracle bulk "save exceptions" 1
oracle forall log errors save exceptions 1
oracle forall save exception 1
oracle forall save exceptions dbms_errlog bulk_exceptions 1
oracle pl sql log errors save exceptions 1
oracle save exceptions forall 1
oracle save exceptions log errors 1
oracle save exceptions log errors for all 1
oracle save exceptions vs log errors 1
pl/sql log errors save exception forall 1
pl/sql save exceptions 1
plsql using log errors and save exception 1
save exceprions 1
save exceptions example 1
save exceptions in oracle sql 1
save exceptions in pl sql 1
save exceptions log error oracle 1
save exeptions 1
use log errors and save exception in forall orcle 1
using save exceptions and log errors in the same forall statement 1
using save exceptions and log errors inside forall pl sql 1
when save exceptions are used in oracle 1

And then I remembered last week's PL/SQL Challenge. On July 8, it had a nice question about what happens when you combine the FORALL SAVE EXCEPTIONS with a LOG ERRORS clause...

And while I'm talking about the PL/SQL Challenge: if you haven't played it yet and you want to learn the language better, then you should give it a try. If you played the game and you haven't learned anything, then at least you'll have a high score :-). The questions are very diverse regarding the topics and regarding difficulty. So there will always be topics with which you are highly familiar and those with which you are not. For example, I use PL/SQL almost 15 years now, but somehow I almost never used the UTL_FILE package. It just seems counterintuitive to me to work with files when you have a database at your disposal. But by playing the quiz I learned the package inside out.

And for those of you who wished me luck and wanted to know how the playoff went: well, it went smooth. So compliments to the developers of the site. The 10 questions itself contained a lot of text and code to read and grasp in just 15 minutes. And I thought the questions were on average tougher than normal. I managed to answer all questions in time, but I had to rush. I'm not so sure about the correctness of all my answers, though. We'll see.