It is not unusual to use MySQL’s YEARWEEK() function to create identifiers for weeks within years. The problem is well-known. You need to store the week number for a certain year. Storing only the week-number decouples your data from the real year. You need to store the year too and that’s when YEARWEEK kicks in. But beware, there are some pitfalls!
MySQL
The YEARWEEK()-function gives us something like 201304 for the fourth week of 2013. But that’s only half the story. Problems arise when you want to know the week-number for 31.12.2012. This could be 201253 or 201301, depending on how you see it. The week could start on monday, or sunday and the first week of the year starts after 4 days or not.
There is an agreement on the week-calculation. It defined as ISO 8601. The first weeks must have at least 4 days and starts on a monday. See http://en.wikipedia.org/wiki/ISO_8601 for more information.
Unfortunately, MySQL uses mode “0” for the week-calculation. It is not the ISO 8601 norm. You must set MySQL to use mode “3” (default_week_format) or pass it as a parameter in the YEARWEEK() function.
| Mode | First day of week | Range | Week 1 is the first week … |
|---|---|---|---|
| 0 | Sunday | 0-53 | with a Sunday in this year |
| 1 | Monday | 0-53 | with more than 3 days this year |
| 2 | Sunday | 1-53 | with a Sunday in this year |
| 3 | Monday | 1-53 | with more than 3 days this year |
| 4 | Sunday | 0-53 | with more than 3 days this year |
| 5 | Monday | 0-53 | with a Monday in this year |
| 6 | Sunday | 1-53 | with more than 3 days this year |
| 7 | Monday | 1-53 | with a Monday in this year |
So, the following problems are solved:
select YEARWEEK("2012-12-31");
gives us 201253.<br />
select YEARWEEK("2012-12-31", 3);
gives us 201301 which adheres to the ISO 8601.
Ok, this problem seems solved. The database seems to have the dates correct. But if I want to query the week, I cannot always rely on the database to calculate me the correct date. Sure, I could do round-trips to the server sending a date and receiving the correct week number. But that’s slow.
Java
Let’s try to rebuild the YEARWEEK() in Java using the ISO 8601 norm. The Calendar-class is not the solution we’re looking for. Sure, you can get the week, but you can’t get the correct year for that week when you’re in the ISO 8601 mode. For example, for 2012-12-31 you get the week 01 but the year 2012, resulting in 201201 for the last week of the year. Which is of course, incorrect!
The package Joda helps us out and provides the solution. Out of legacy-grounds, the API works with a calendar object. Joda provides us the correct week in the year and the correct year for the that week (even though the real year is different).
I wrote a test-class with the Java-method to generate the values.
import org.joda.time.DateTime;
// ....
/**
* Return the ISO 8601 format of YEARWEEK with a given calendar.
*/
public int from(final Calendar calendar) {
DateTime dt = new DateTime(calendar) ;
return dt.weekyear().get() * 100 + dt.weekOfWeekyear().get();
}
I’ve tested the results agains the MySQL YEARWEEK for 12 years and all seems to work fine!
Brain/Drain/Pain 