Planet MySQL :: Planet MySQL - Archives - Easter Eggs for MySQL and Kettle

To whom it may concern,
A MySQL stored function to calculate easter day
I uploaded a MySQL forge snippet for the f_easter() function. You can use this function in MySQL statements to calculate easter sunday for any given year:


mysql>  select f_easter(year(now()));
+-----------------------+
| f_easter(year(now())) |
+-----------------------+
| 2010-04-04            |
+-----------------------+
1 row in set (0.00 sec)

Anonymous Gregorian algorithm
To implement it, I simply transcribed the code of the "Anonymous Gregorian algorithm" from wikipedia's Computus article.

You might ask yourself: "how does it work?". Frankly, I don't know. Much like a tax form, I treat the calculation as a black box. But, it's wikipedia, so it must be right, right?
A Javascript snippet to calculate easter day
I also transcribed the algorithm to javascript, so I could use it in Kettle (a.k.a. Pentaho Data Integration). Of course, nothing should stop you from using it for another environment, such as a webpage.

I don't have a proper place to host that code, so I'm listing it here:


//Script to calculate Easter day
//according to the "Anonymous Gregorian algorithm" 
function easterDay(year) {
     var a = year % 19,
     b = Math.floor(year / 100),
     c = year % 100,
     d = Math.floor(b / 4),
     e = b % 4,
     f = Math.floor((b + 8) / 25),
     g = Math.floor((b - f + 1) / 3),
     h = (19 * a + b - d - g + 15) % 30,
     i = Math.floor(c / 4),
     k = c % 4,
     L = (32 + 2 * e + 2 * i - h - k) % 7,
     m = Math.floor((a + 11 * h + 22 * L) / 451),
     n = h + L - 7 * m + 114;
     return new Date(year,
                  Math.floor(n / 31) - 1,
                  (n % 31) + 1);
}

easter = easterDay(year);

To use this in your kettle transformations, create a stream with an field of the Integer type called year. The year field should take on the value of some year. In the step, create one output field of the Date type to take on the value of the easter script variable. (For usage outside kettle, just use the easterDay() function as you see fit)
Nice, but so what?
The thought may have crossed your mind: "So what, who cares - why should I ever want to know when it's easter day?"

Apparently, if you think like that, you don't like eggs very much. That's ok - I don't blame you. But I happen to like eggs, and people in the egg business like people that like eggs like me so they can sell them more eggs. In fact, they like selling eggs so much, that it makes a big difference to them whether their business intelligence reports say: "On March 22, 2008, we sold 10 times more eggs than on February 22 and May 22 of the same year" as compared to "In 2008, on the day before Easter, we only sold half the amount of eggs as compared to the day before Easter in 2009".

In order to report these facts, special events and holidays like easter are stored in a date dimension. (I wrote about creating a localized date dimension, a date dimension that speaks your language some time ago)

So there you go: you could use these solutions in order to build a date dimension that understands easter. The nice thing about easter is that it can be used to derive a whole bunch of other Christian holidays, like good friday, ascension, and pentecost, and in many western countries, these will be special days with regard to the normal course of business. I leave all these as an exercise to the reader, but trust me - calculating easter is the key to a solving a lot of these problems.

MySQL Links