Anonymous Gregorian algorithm

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:

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.

pardon my ignorance - I wasn't aware of such a date. The Christian holidays I referred to can all be calculated by simple addition / substraction of days. You're implying the calculation of Orthodox Easter is not trivial, and I am the last one to doubt it. Should I have been aware of any hairy issues, I would have mentioned them.

Of course, if you can spare us a moment, feel free to elaborate what Orthodox Easter is, and if you have a way to calculate it, it'd be great if you could share that too.

Easter is calculated based on the lunar calendar and the Spring equinox (Northern Hemisphere), which means you should be able to use a similar formula for Passover, Ramadan and other important religious holidays.

Gerry, yes, indeed, easter is based on the lunar cycle. But from what I understand after reading a bit on the subject, there's also some convention and synchronization with the ecclestial calendar going on.

Anyway, I did look for calculations for other holidays as well, and found at least some that seem reasonably doable for Passover and Rosh Hashannah:

nice post, let me say that I had 0 experience with Kettle, but after buying your (and Jos) book I learned to work with this tool and also many other things about Pentaho. I even implement this algorithm transformation... it took me less than 2 mins! (http://bit.ly/b1292s)

This post give me the idea to complement my date dimension with other religious holidays, there many in the form "The first day of month's nn week".

It would be very nice to have a toolkit for special/holidays. But the special days and holidays are always very dependent to what country/religion you are building information for. Most of the floating holidays are Easter related (as for my knowledge) and in my case (I'm working with Portuguese and Spanish data), besides all the static holidays, I just needed to calculate Easter and three other holidays: First one is 47 days before easter, second one is 2 days before easter and the other is 60 days after easter.

I thing it is an interesting Idea, but I'm not seeing a way to do that without having to make cases for a considerable big set of countries.

Indeed. The ones I care about are either fixed on a certain date (new years day, christmas, queens day, liberation day) with a regular recurrence, or the christian holidays that can be derived from easter: good friday (friday before easter), ascension (40 days after easter) and pentecost (10 days after ascension).

What's with 47 days before easter and 60 days after easter?

Regarding locality: taking that into account was exactly the idea. It is doable to manually set up special cases for one country/locality. The challenge would be to come up with something that is readily usable even if you're doing an ETL gig outside your country.

The movable holidays I need for Portugal are:Carnival - 47 days before EasterGood Friday - 2 days before EasterCorpus Christi (feast) - 60 days after Easter

More info:http://en.wikipedia.org/wiki/Public_holidays_in_Portugal

As for the toolkit, it could be built to work with country related information based on a local parameter and using some kind of DB, web service or any kind of properties file. The information might be incrementally added by the users and shared to all.

In my case, I have a SQL procedure to handle time dimension but it would be very nice to have a ETL step (on kettle) to handle this task and automatically set special days.

But I think that in order to make a complete toolkit to populate time dimensions, a way to define Business special days and boundaries should be considered. Depending on the brand/business in case, they are very subjective too.

In my opinion, your idea is very good and it is of good help for every ETL designer for sure.

Regarding locality: taking that into account was exactly the idea. It is doable to manually set up special cases for one country/locality. The challenge would be to come up with something that is readily usable even if you're doing an ETL gig outside your country.

I think it's not really worth waiting until you find a generic, good-for-all-cases approach. What I think does make sense is create a toolkit of utilities that help one tackle common or at least repeatedly occurring cases.

If a few people would share their stuff, i think such a toolkit could grow quite quickly.

Hi All,I have Table called Test with some fields like Id NOTNULL,Name NOTNULL,Amount Null.when i inserted data into test table like ID and Amount,the trigger has to be raised.and i want to display user msg like name should must eneter.how to achieve this by using triggers

Search This Blog

About Me

I'm Roland Bouman (@rolandbouman on twitter). I'm a software (web) application developer and I work on both the front end as well as the back end. I do data modeling, database design, ETL, Analytics, and Business Intelligence.