Excluding English Public Holidays from a DBMS_SCHEDULER Schedule

Posted on August 23, 2022

This week we’re taking on the entire Establishment through the medium of… DBMS_SCHEDULER Calendar Strings.
I should probably explain that, in a normal year, England has eight Public Holidays. Four of these are based around religious festivals. Whilst Christmas Day is always on 25th December, working out when Easter falls requires some complex calculations.
On top of that, there are times when the secular holidays are re-arranged and even days added. These are usually due to some Royal anniversary or event.
What I want to explore is how we can construct DBMS_SCHEDULER calender string s which include working days ( in this case week-days) but exclude Public Holidays.
The three specific examples I’d like to end up with are :

a schedule for the first working day of each month
a schedule for the last working day of each month
a schedule for the working day on or immediately after the 6th of each month ( which we’ll say is Pay day)

In order to achieve this we’ll :

explore how the DBMS_SCHEDULER calendar syntax can help us to calculate when each holiday will fall
write a PL/SQL function to return the date of Easter Sunday for a given year
create a table to hold the holiday dates and also to allow any changes to be recorded
write a procedure to maintain the table
write a procedure to dynamically build and maintain a Schedule of Public Holiday dates
build the three schedules we want using the Public Holidays Schedule to exclude the Public Holidays.

The code in what follows can be found in this Github repo

Public Holidays in England

The regular Public Holidays in England and Wales (Scotland and Northern Ireland have their own variations) are :

New Years Day Holiday – the first weekday of the year
Good Friday – the Friday before Easter Sunday
Easter Monday – the day after Easter Sunday
May Day – the first Monday in May
Spring Bank Holiday – the last Monday in May
August Bank Holiday – the last Monday in August
Christmas Day Holiday – the first weekday on or after 25th December
Boxing Day Holiday – the second weekday on or after 25th December

Using Calendar Strings to Calculate Dates

DBMS_SCHEDULER’s calendaring syntax does allow for quite a bit of flexibility and there are several ways to achieve the same result.
This is covered fairly comprehensively in the documentation, but hopefully the following examples will prove useful to future me when I’m skim-reading this in a hurry…

First Monday in May ( May Day Holiday)

'freq=yearly; bymonth=5; byday=mon; bysetpos=1'

bymonth=5 is the 5th month of the Year (May).
byday=mon – we’re only interested in Mondays
bysetpos=1 – we want the earliest date that matches

Last Monday in May ( Spring Bank Holiday)

'freq=yearly; bydate=0531-span:7d; byday=mon; bysetpos=1'

bydate=0531-span:7d – evaluate from the 31st May but go back ( –span) up to 7 days

Last Monday in August (August Bank Holiday)

Just for a change, let’s try a different approach to the one we’ve used for Spring Bank Holiday :

'freq=monthly; bymonth=8; byday=-1mon'

byday=-1mon – find the last Monday of the month.

Note that unlike the other calendar strings we’ve used, which have a frequency of yearly, this one is set to monthly. That’s becasue using yearly with this form of the byday syntax will give you :

ORA-27419: unable to determine valid execution date from repeat interval

Christmas Day and New Year’s Day holidays are similar in that they occur on the first week day on or after a specific date.
Boxing Day occurs on the second week day on or after 25th December.

Christmas Day Holiday and Boxing Day

For Christmas Day Holiday :

'freq=yearly; bydate=1225+span:7d; byday=mon,tue,wed,thu,fri; bysetpos=1'

Boxing Day is almost exactly the same a Christmas by bysetpos is set to 2 instead of 1:

'freq=yearly; bydate=1225+span:7d; byday=mon,tue,wed,thu,fri; bysetpos=2'

Whilst the syntax in these examples is similar to that in the string we’ve used for the last Monday in May, here we’re searching forward from the date specified rather than backward, as denoted by the “+” in the bydate string :

bydate=1225+span:7d

New Year’s Day Holiday

We’re using the same approach as that for the Christmas and Boxing Day Holidays :

'freq=yearly; bydate=0101+span:7d; byday=mon,tue,wed,thu,fri; bysetpos=1'

We’ll be using DBMS_SCHEULER.EVALUATE_CALENDAR_STRING shortly, but you can use it as described here if you want to play with any of the above.

Now for the “fun” bit…

Easter

It seems that, if your a Major Abrahamic Religion then an arcane mathematical calculation is mandatory to work out when at least some of your holy days are.
Some years ago, I implemented the Easter algorithm in PL/SQL.

I’ve re-created it as part of the package I’m going to use to bring all this calander malarky together.

The only thing I can really say about it is that it works. I don’t know how. I guess you just need to have a bit of faith…

...

function calculate_easter( i_year in number default null)

return date

is

a pls_integer;

b pls_integer;

c pls_integer;

d pls_integer;

e pls_integer;

f pls_integer;

g pls_integer;

h pls_integer;

i pls_integer;

k pls_integer;

l pls_integer;

m pls_integer;

p pls_integer;

v_month pls_integer;

v_day pls_integer;

v_year pls_integer;

v_easter_sunday date;

begin

v_year := nvl(i_year, extract( year from sysdate));

a := mod( v_year, 19);

b := floor(v_year/100);

c := mod(v_year,100);

d := floor(b/4);

e := mod(b,4);

f := floor((b+8)/25);

g := floor((b-f+1)/3);

h := mod((19*a+b-d-g+15),30);

i := floor(c/4);

k := mod(c,4);

l := mod((32+2*e+2*i-h-k),7);

m := floor((a+11*h+22*l)/451);

v_month := floor((h+l-7*m+114)/31); -- 3=March, 4=April

p := mod((h+l-7*m+114),31);

v_day := p+1 ; -- date in Easter Month

v_easter_sunday := to_date(v_day||'-'||v_month||'-'||v_year, 'DD-MM-YYYY');

return v_easter_sunday;

end calculate_easter;

...

The Public Holidays table

The table looks like this :

create table public_holidays(

holiday_date date constraint public_holidays_pk primary key,

holiday_name varchar2(100) not null,

notes varchar2(4000))

/

Using the HOLIDAY_DATE as the Primary Key means that we should be careful to ensure that it only holds a truncated date value :

create or replace trigger public_holidays_bi_trg

before insert on public_holidays

for each row

begin

:new.holiday_date := trunc(:new.holiday_date);

end;

/

Putting the dates in your diary

In order to keep our schedule…er…up-to-date, I’ve wrapped up the code into a neat(ish) package :

create or replace package maintain_public_holidays

as

function calculate_easter( i_year in number default null) return date;

procedure add_standard_holidays( i_year in number);

procedure generate_schedule;

end;

/

create or replace package body maintain_public_holidays

as

function calculate_easter( i_year in number default null)

return date

is

a pls_integer;

b pls_integer;

c pls_integer;

d pls_integer;

e pls_integer;

f pls_integer;

g pls_integer;

h pls_integer;

i pls_integer;

k pls_integer;

l pls_integer;

m pls_integer;

p pls_integer;

v_month pls_integer;

v_day pls_integer;

v_year pls_integer;

v_easter_sunday date;

begin

v_year := nvl(i_year, extract( year from sysdate));

a := mod( v_year, 19);

b := floor(v_year/100);

c := mod(v_year,100);

d := floor(b/4);

e := mod(b,4);

f := floor((b+8)/25);

g := floor((b-f+1)/3);

h := mod((19*a+b-d-g+15),30);

i := floor(c/4);

k := mod(c,4);

l := mod((32+2*e+2*i-h-k),7);

m := floor((a+11*h+22*l)/451);

v_month := floor((h+l-7*m+114)/31); -- 3=March, 4=April

p := mod((h+l-7*m+114),31);

v_day := p+1 ; -- date in Easter Month

v_easter_sunday := to_date(v_day||'-'||v_month||'-'||v_year, 'DD-MM-YYYY');

return v_easter_sunday;

end calculate_easter;

procedure add_standard_holidays( i_year in number)

is

type rec_holidays is record (

holiday_name public_holidays.holiday_name%type,

calendar_string varchar2(4000),

holiday_date date);

type typ_holidays is table of rec_holidays

index by pls_integer;

tbl_holidays typ_holidays;

v_start_date date;

v_easter_sunday date;

begin

-- We'll be using this as the RETURN_DATE_AFTER parameter in the call to

-- DBMS_SCHEDULER.EVALUATE_CALENDAR_STRING so set it to the last day of the year

-- *before* the one we want to add dates for

v_start_date := to_date(i_year -1||'1231', 'YYYYMMDD');

tbl_holidays(1).holiday_name := 'NEW YEARS DAY';

tbl_holidays(1).calendar_string := 'freq=yearly; bymonth=1; byday=mon,tue,wed,thu,fri; bysetpos=1';

tbl_holidays(2).holiday_name := 'MAY DAY';

tbl_holidays(2).calendar_string := 'freq=yearly; bydate=0501+span:7D; byday=mon; bysetpos=1';

tbl_holidays(3).holiday_name := 'SPRING BANK HOLIDAY';

tbl_holidays(3).calendar_string := 'freq=yearly; bydate=0531-span:7D; byday=mon; bysetpos=1';

tbl_holidays(4).holiday_name := 'AUGUST BANK HOLIDAY';

tbl_holidays(4).calendar_string := 'freq=yearly; bydate=0831-span:7D; byday=mon; bysetpos=1';

tbl_holidays(5).holiday_name := 'CHRISTMAS DAY';

tbl_holidays(5).calendar_string := 'freq=yearly; bydate=1225+span:7d; byday=mon,tue,wed,thu,fri; bysetpos=1';

tbl_holidays(6).holiday_name := 'BOXING DAY';

tbl_holidays(6).calendar_string := 'freq=yearly; bydate=1225+span:7d; byday=mon,tue,wed,thu,fri; bysetpos=2';

for i in 1..tbl_holidays.count loop

dbms_scheduler.evaluate_calendar_string(

calendar_string => tbl_holidays(i).calendar_string,

start_date => null,

return_date_after => v_start_date,

next_run_date => tbl_holidays(i).holiday_date);

end loop;

v_easter_sunday := calculate_easter( i_year);

tbl_holidays(7).holiday_name := 'GOOD FRIDAY';

tbl_holidays(7).holiday_date := v_easter_sunday - 2;

tbl_holidays(8).holiday_name := 'EASTER MONDAY';

tbl_holidays(8).holiday_date := v_easter_sunday + 1;

for j in 1..tbl_holidays.count loop

dbms_output.put_line(tbl_holidays(j).holiday_name||' : '||to_char(tbl_holidays(j).holiday_date, 'DD-MON-YYYY'));

insert into public_holidays( holiday_name, holiday_date, notes)

values( tbl_holidays(j).holiday_name, tbl_holidays(j).holiday_date, 'Generated record');

end loop;

end add_standard_holidays;

procedure generate_schedule

is

C_SCHEDULE_NAME constant user_scheduler_schedules.schedule_name%type := 'PUBLIC_HOLIDAYS_ENGLAND';

v_calendar_string user_scheduler_schedules.repeat_interval%type;

v_start_date date;

v_placeholder pls_integer;

v_exists boolean;

cursor c_schedule_exists is

select null

from user_scheduler_schedules

where schedule_name = C_SCHEDULE_NAME;

begin

v_calendar_string := 'freq=yearly;bydate=';

for r_hols in (

select to_char(holiday_date, 'YYYYMMDD') as string_date

from public_holidays

order by holiday_date)

loop

v_calendar_string := v_calendar_string||r_hols.string_date||',';

end loop;

-- strip off the last ','

v_calendar_string := rtrim(v_calendar_string, ',');

dbms_output.put_line(v_calendar_string);

open c_schedule_exists;

fetch c_schedule_exists into v_placeholder;

v_exists := c_schedule_exists%found;

close c_schedule_exists;

if v_exists then

dbms_scheduler.set_attribute( C_SCHEDULE_NAME, 'repeat_interval', v_calendar_string);

else

dbms_scheduler.create_schedule(

schedule_name => C_SCHEDULE_NAME,

repeat_interval => v_calendar_string,

comments => 'Bank Holidays in England');

end if;

end generate_schedule;

end maintain_public_holidays;

/

Note that DBMS_OUTPUT statements are included for illustrative purposes.

Let’s use the standard holiday dates for 2022 for the initial table population :

If we check the table, we can confirm that we have the expected holidays :

select holiday_date, holiday_name, notes

from public_holidays

where extract( year from holiday_date) = 2022

order by holiday_date

/

HOLIDAY_DATE    HOLIDAY_NAME                   NOTES                                   
--------------- ------------------             ----------------
03-JAN-2022     NEW YEARS DAY                  Generated record                        
15-APR-2022     GOOD FRIDAY                    Generated record                        
18-APR-2022     EASTER MONDAY                  Generated record                        
02-MAY-2022     MAY DAY                        Generated record                        
30-MAY-2022     SPRING BANK HOLIDAY            Generated record                        
29-AUG-2022     AUGUST BANK HOLIDAY            Generated record                        
26-DEC-2022     CHRISTMAS DAY                  Generated record                        
27-DEC-2022     BOXING DAY                     Generated record                        

8 rows selected.

Now we can use these dates for the initial Public Holidays Schedule creation :

We can check the details of the schedule in the Data Dictionary :

select schedule_type, repeat_interval, comments

from user_scheduler_schedules

where schedule_name = 'PUBLIC_HOLIDAYS_ENGLAND'

/

State Intervention

Just to make things interesting, the Bank Holidays have been altered in 2022.
The Spring Bank Holiday has been moved and an extra holiday has been added to commemorate the Queen’s Platinum Jubilee.

In DML terms, the effect on our data is :

delete from public_holidays

where holiday_date = date '2022-05-30'

and holiday_name = 'SPRING BANK HOLIDAY';

insert into public_holidays( holiday_date, holiday_name, notes)

values(date '2022-06-02', 'SPRING BANK HOLIDAY', q'[Moved for the Queen's Platinum Jubilee]');

insert into public_holidays( holiday_date, holiday_name, notes)

values(date '2022-06-03', 'PLATINUM JUBILEE', q'[The Queen's Platinum Jubilee]');

commit;

…which means that the Holidays for 2022 are now :

select holiday_date, holiday_name, notes
from public_holidays
where extract(year from holiday_date) = 2022
order by holiday_date
/

HOLIDAY_DATE HOLIDAY_NAME              NOTES                                      
------------ -----------------         -------------------
03-JAN-2022  NEW YEARS DAY             Generated record                           
15-APR-2022  GOOD FRIDAY               Generated record                           
18-APR-2022  EASTER MONDAY             Generated record                           
02-MAY-2022  MAY DAY                   Generated record                           
02-JUN-2022  SPRING BANK HOLIDAY       Moved for Platinum Jubilee     
03-JUN-2022  PLATINUM JUBILEE          The Queen's Platinum Jubilee               
29-AUG-2022  AUGUST BANK HOLIDAY       Generated record                           
26-DEC-2022  CHRISTMAS DAY             Generated record                           
27-DEC-2022  BOXING DAY                Generated record                           

9 rows selected.

Fortunately, we can keep the Schedule in synch by re-running the maintain_schedule procedure :

Schedule Maintenance

You may be struck by the fact that there’s no functionality to curate the dates in the PUBLIC_HOLIDAYS, by deleting dates in the past for example.
Also the GENERATE_SCHEDULE procedure doesn’t bother checking to see if anything has changed before it goes ahead and updates the Schedule.
The reason for this is that we’re dealing with a rather small and fairly static data set. Remember, under normal circumstances there are eight Bank Holidays in a year.
The limiting factor for the number of individual dates that can be included a Schedule is the maximum size of the REPEAT_INTERVAL, which (up to and including 21c) appears to be 4000 judging by the definition of USER_SCHEDULER_SCHEDULES.

For the PUBLIC_HOLIDAYS_ENGLAND Schedule, the first 19 characters are taken up with :

"'freq=yearly;bydate='"

I’ve chosen to put the dates in the string using the ISO date format – YYYYMMDD. In additon, each date has a single ‘,’ character as a separator in the list. Therefore, one date takes up 9 characters.

This means that, assuming we’re using a single byte character set, we’ve got enough space to hold roughly 440 dates which, unless we suddenly get a lot of additional holidays, is around 55 years worth.

I’ll probably refrain from adding all of those dates for now, but no harm in doing the next few years…

set serverout on size unlimited

clear screen

declare

v_start_year number(4);

v_end_year number(4);

v_range number := 5;

begin

-- find the latest year for which we have holidays defined

select extract( year from max(holiday_date))

into v_start_year

from public_holidays;

v_start_year := v_start_year + 1;

v_end_year := v_start_year + v_range;

for i in v_start_year..v_end_year loop

dbms_output.put_line('Adding Standard Holidays for '||i);

maintain_public_holidays.add_standard_holidays(i);

end loop;

commit;

end;

/

exec maintain_public_holidays.generate_schedule;

select repeat_interval
from user_scheduler_schedules
where schedule_name = 'PUBLIC_HOLIDAYS_ENGLAND'
/

REPEAT_INTERVAL                                                                 
--------------------------------------------------------------------------------
freq=yearly;bydate=20220103,20220415,20220418,20220502,20220602,20220603,2022082
9,20221226,20221227,20230102,20230407,20230410,20230501,20230529,20230828,202312
25,20231226,20240101,20240329,20240401,20240506,20240527,20240826,20241225,20241
226,20250101,20250418,20250421,20250505,20250526,20250825,20251225,20251226,2026
0101,20260403,20260406,20260504,20260525,20260831,20261225,20261228,20270101,202
70326,20270329,20270503,20270531,20270830,20271227,20271228,20280103,20280414,20
280417,20280501,20280529,20280828,20281225,20281226

The Final Calendar Strings

With this schedule in place, we can now define our required calendar strings.

Note that you can get the output below by running final_schedules.sql, which is included in the Repo.

The first working day of each month is :

'freq=monthly; byday=mon,tue,wed,thu,fri; exclude=public_holidays_england; bysetpos=1'

For 2022, the dates returned are :

First Working Day for each month in 2022
Tuesday   4th January 2022
Tuesday   1st February 2022
Tuesday   1st March 2022
Friday    1st April 2022
Tuesday   3rd May 2022
Wednesday 1st June 2022
Friday    1st July 2022
Monday    1st August 2022
Thursday  1st September 2022
Monday    3rd October 2022
Tuesday   1st November 2022
Thursday  1st December 2022

The last working day of the month is :

'freq=monthly; byday=mon,tue,wed,thu,fri; exclude=public_holidays_england; bysetpos=-1'

which returns :

Last Working Day for each month in 2022
Monday    31st January 2022
Monday    28th February 2022
Thursday  31st March 2022
Friday    29th April 2022
Tuesday   31st May 2022
Thursday  30th June 2022
Friday    29th July 2022
Wednesday 31st August 2022
Friday    30th September 2022
Monday    31st October 2022
Wednesday 30th November 2022
Friday    30th December 2022

Pay Day ( closest working day to 6th of the month) is :

'freq=monthly; bymonthday=6,7,8,9,10; byday=mon,tue,wed,thu,fri; exclude=public_holidays_england;bysetpos=1'

resulting in :

Pay Day for each month in 2022
Thursday  6th January 2022
Monday    7th February 2022
Monday    7th March 2022
Wednesday 6th April 2022
Friday    6th May 2022
Monday    6th June 2022
Wednesday 6th July 2022
Monday    8th August 2022
Tuesday   6th September 2022
Thursday  6th October 2022
Monday    7th November 2022
Tuesday   6th December 2022

Excluding English Public Holidays from a DBMS_SCHEDULER Schedule

Excluding English Public Holidays from a DBMS_SCHEDULER Schedule

Public Holidays in England

Using Calendar Strings to Calculate Dates

First Monday in May ( May Day Holiday)

Last Monday in May ( Spring Bank Holiday)

Last Monday in August (August Bank Holiday)

Christmas Day Holiday and Boxing Day

New Year’s Day Holiday

Easter

The Public Holidays table

Putting the dates in your diary

State Intervention

Schedule Maintenance

The Final Calendar Strings

Recommend

Survey: Have You Experienced Incivility at Work?

Don’t-miss Spark sessions to level up your FullStory knowledge | FullStory

微信朋友圈能修改可见范围了目前还在内测中

SwiftUI Button Tutorial: Customization [FREE]

Advanced PostgreSQL with Vapor [FREE]

“元宇宙的微软”影创科技被曝大规模欠薪，创始人孙立解散公司大群后失联

狗狗币“内战”？支持马斯克的创始人拉黑反对马斯克的创始人

Tim Cook, Jony Ive, and Laurene Powell Jobs to Speak at Code 2022 Conference Nex...

Dow leads stocks lower after run of downbeat economic data

One step closer to the localization of semiconductor sensor technology

About Joyk