HANA Tips & Tricks: issue #2 – Calculating Easter and related holidays with a HANA scalar function
About this Post
At Just-BI we recently launched a monthly knowledge-sharing initiative where our consultants and developers discuss any issues and share tips & tricks concerning SAP HANA development. We share any insights here on SCN. About a month ago we published our first post: HANA Tips & Tricks: issue #1 – Hacking information views
We just had our second HANA open Mic session, and we decided to start publishing one post for each topic. In this post, I will explain how to calculate the date of easter for any given year. Please also check out other posts in this month’s series:
Calculating Easter Day
In today’s post, I want to share a SAP HANA scalar function that calculates easter day for a given year. Many countries throughout the world observe a number of holidays which can all be derived from easter day. Being able to calculate these holidays for arbitrary years is useful for any planning or calendaring application, as well as for analytical applications. For example, one could adorn a date dimension table with attributes that indicate whether a particular date is a holiday, and what type of holiday it is. In such cases, a function that calculates easter day will come in handy to compute at least some of the holidays.
Unlike for example Christmas and New Year’s day, Easter is a so-called moveable feast – its date is not fixed, but changes from one year to the next. In many cases this is caused by the fact that some component of the date calculation relies on a moon calendar. This is also the case with easter. If you’re interested in the background and details of the calculation, wikipedia has an excellent article on this topic.
The wikipedia article also offers a number of algorithms to do the actual calculation. Since medieval times, these calculations are known as “Computus”, which simply mean “Calculation”. I lifted one particular algorithm from the wikpedia article, the so-called Anonymous Gregorian Algorithm, and wrote it as a SAP/HANA scalar function. It goes like this:
CREATE FUNCTION f_easter_day ( p_year smallint ) RETURNS p_easter_date date LANGUAGE SQLSCRIPT SQL SECURITY INVOKER AS BEGIN declare v_year smallint default ifnull(p_year, year(current_date)); DECLARE a SMALLINT DEFAULT mod(v_year, 19); DECLARE b SMALLINT DEFAULT FLOOR(v_year / 100); DECLARE c SMALLINT DEFAULT mod(v_year, 100); DECLARE d SMALLINT DEFAULT FLOOR(b / 4); DECLARE e SMALLINT DEFAULT mod(b, 4); DECLARE f SMALLINT DEFAULT FLOOR((b + 8) / 25); DECLARE g SMALLINT DEFAULT FLOOR((b - f + 1) / 3); DECLARE h SMALLINT DEFAULT mod((19 * a + b - d - g + 15), 30); DECLARE i SMALLINT DEFAULT FLOOR(c / 4); DECLARE k SMALLINT DEFAULT mod(c, 4); DECLARE L SMALLINT DEFAULT mod((32 + 2 * e + 2 * i - h - k), 7); DECLARE m SMALLINT DEFAULT FLOOR((a + 11 * h + 22 * L) / 451); DECLARE v100 SMALLINT DEFAULT h + L - 7 * m + 114; p_easter_date = to_date( v_year||'-'||floor(v100 / 31)||'-'||(mod(v100, 31)+1) , 'YYYY-MM-DD' ); END;
As you can see, the function f_easter_day takes a p_year parameter, which is an SMALLINT representing the year for which you’d like to calculate easter day in that year. The return value is a DATE which represents the actual date that marks easter sunday in the given year.
The code is positively obscure – I do not pretend I can explain exactly how this works. However, I have tested it and so far the function really does seem to work.If you are interested in how it works, then please review the wikipedia article.
Let’s Calculate some Holidays!
The following code sample illustrates how to calculate easter day, as well as a number of holidays that may be directly derived from easter day:
- Ash Wednesday (End of Carnival – Start of Lent)
- Mandy Thursday
- Good Friday
with easter_days as ( select f_easter_day(generated_period_start) easter_day from series_generate_smallint(1, year(current_date), year(current_date) + 10) ) select add_days(easter_day, -46) ash_wednesday , add_days(easter_day, -3) maundy_thursday , add_days(easter_day, -2) good_friday , easter_day , add_days(easter_day, 39) ascension_thursday , add_days(easter_day, 49) pentecost from easter_days
As you can see, we use the SAP HANA table function SERIES_GENERATE_SMALLINT()to generate a set of integers, starting from the current year, and ending 10 years from now. The result looks like:
I hope you enjoyed this post and I hope you’ll find the f_easter_day() function useful! If you have some tips on calculating holidays, or if you have alternative ways to calculate easter day, then by all means – leave a comment to discuss! We love your feedback.
In the mean while, stay tuned for more SAP HANA tips and tricks by searching for the hanatipsandtricks hashtag on SCN. Thank you for your time!