Vitually every data warehouse have a date dimension. Also almost every buisness user require a weekly reports (along with daily, monthly and other). At this point report developer often uses functions that return week's number in a year. Trouble may arrise if you decide to use together functions from different languages and technologies.
In this article I'll compare week function in Java, JavaScript, MySQL and MDX(Mondrian). You'll see that without proper attention the same date could appear in different weeks when using different technologies together. For example 1-st Jan 2010 could be 0-th week in MySQL, 1-st week in Java, and 53-rd week somewhere else.
If you interested in aspects of generating date dimension I would recommend to read Roland Bouman's article of this topic.
Java: Calendar
Java provides class java.util.Calendar for operating with dates. Calendar class have a constant WEEK_OF_MONTH for getting week's number. Also Calendar have two properties for configuring week's number calculations: minimalDaysInFirstWeek and firstDayOfWeek. Default values for these properties are locale-dependent.
Details:
- firstDayOfWeek - first day of a week(Sunday, Monday,.. Saturday).
- minimalDaysInFirstWeek - minimal number of days in first week that are also in this year.
- first week have number 1.
Example:
java.util.Calendar calendar = java.util.Calendar.getInstance();
java.util.Date date = (new java.text.SimpleDateFormat("yyyy-MM-dd")).parse("2010-12-31");
calendar.setTime(date);
int week = calendar.get(java.util.Calendar.WEEK_OF_YEAR);
System.out.println(week); // prints: 52
MySQL: WEEK
MySQL have a WEEK function. It also allows user to change calculation algorithm, but it's not as generic as in Java.
In MySQL user can choose between two main approaches for determining the first week of the year:
- It is the first week that starts in this year. Read as: Sunday or Monday in this year. (American approach)
- It is the week that contain January 1-st, if more that 3 days of this week are in the new year. (European approach)
This discussion may help to understand that.
If a date falls in the last week of the previous year, MySQL may return
0 in certain configurations.Example:
select WEEK(STR_TO_DATE('Dec 31, 2009','%M %d,%Y')) ; - 52
select WEEK(STR_TO_DATE('Jan 1, 2010','%M %d,%Y')) ; - 0
select WEEK(STR_TO_DATE('Dec 31, 2009','%M %d,%Y'), 3) ; - 53
select WEEK(STR_TO_DATE('Jan 1, 2010','%M %d,%Y'), 3) ; - 53
MDX: DatePart.
MDX uses VisualBasic's function: DatePart. It allows setting first day of week to any day you like. And for first week of the year it provides following options:
- Use system setting
- Start with the week in which January 1 occurs (default)
- Start with the week that has at least four days in the new year
- Start with the first full week of the new year
JavaScript
JavaScript don't have a standard function for week number. So be careful when writing your own, or using functions provided with JavaScript libraries and when you want to use it together with Java's , MySQL's or MDX's functions.
How to use them together?
The straightest approach is to avoid using them together. You may pre-generate date dimension with help of Java for example. And when later you need to determine week's number by date you may use either Java or date dimension's table.
However if you still need to use week number functions from a mix of technologies, be sure that you understand how each function works, and configure them properly.
