MySQL YEARWEEK() function in Java (ISO 8601)

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:

gives us 201253.
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.


I've tested the results agains the MySQL YEARWEEK for 12 years and all seems to work fine!

The best DROP-script for Mysql!

There are many situations in which you want to drop all tables in a MySQL database. You can easily drop the schema, but then all the permissions are lost too. There are several solutions out there, but they all require some manual effort. I wanted to get rid off it once and for all and constructed this easy script. With the following evil shell script you can easily drop all tables in your database.

Premisse: it uses a group on table_catalog which may not always be the correct grouping in your database. Please, feel free to adapt!

Just create a textfile like drop.sh and give it appropriate rights to execute. The paste the following content in the file and save it.


Use ./drop.sh root root my_db et voilá.... all tables are gone.

Off course, things can be improved. I need to check the parameters and add some documentation...