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.
