MySQL WEEK() function weirdness

Monday, April 13th, 2009

The other day I had to gather some statistics from a database, and the statistics needed to be grouped and filtered by weeknumbers. I ran into something a bit unexpected.

Let’s say we have the follow table definition:

CREATE TABLE test (
  start DATETIME NOT NULL
);

We insert the following dates for testing purposes:

INSERT INTO test VALUES ('2008-12-27');
INSERT INTO test VALUES ('2008-12-28');
INSERT INTO test VALUES ('2008-12-29');
INSERT INTO test VALUES ('2008-12-30');
INSERT INTO test VALUES ('2008-12-31');
INSERT INTO test VALUES ('2009-01-01');
INSERT INTO test VALUES ('2009-01-02');
INSERT INTO test VALUES ('2009-01-03');
INSERT INTO test VALUES ('2009-01-04');
INSERT INTO test VALUES ('2009-01-05');

Those dates span the last week of last year, and the first week of the new year. Now, let’s see what happens when we select the weeknumber from this data using MySQL’s WEEK() function:

mysql> SELECT start, WEEK(start) FROM test;
+---------------------+-------------+
| start               | WEEK(start) |
+---------------------+-------------+
| 2008-12-27 00:00:00 |          51 | 
| 2008-12-28 00:00:00 |          52 | 
| 2008-12-29 00:00:00 |          52 | 
| 2008-12-30 00:00:00 |          52 | 
| 2008-12-31 00:00:00 |          52 | 
| 2009-01-01 00:00:00 |           0 | 
| 2009-01-02 00:00:00 |           0 | 
| 2009-01-03 00:00:00 |           0 | 
| 2009-01-04 00:00:00 |           1 | 
| 2009-01-05 00:00:00 |           1 | 
+---------------------+-------------+
10 rows in set (0.00 sec)

As you can see, we get four different weeks for a timespam of only ten days! Apparently, MySQL counts the first days of the year that do not belong to week 1 as week 0. This was certainly not what I expected, as I’m used to calendars that display the last days of the previous year and the first days of the new year as week 1.

MySQL’s default WEEK() function could have caused serious data skew for me in this case. Fortunately, using the WEEK() function was too slow (as it had to calculate the weeknumber for each row in the result due to a WHERE WEEK(column) BETWEEN x AND y clause in my query), so we calculated the weeknumber using Unix timestamps ourselves. That’s when we found the error. When using the WHERE clause mentioned, and the above data, we could have gotten:

mysql> SELECT * FROM test WHERE WEEK(start) BETWEEN 1 AND 52;
+---------------------+
| start               |
+---------------------+
| 2008-12-27 00:00:00 | 
| 2008-12-28 00:00:00 | 
| 2008-12-29 00:00:00 | 
| 2008-12-30 00:00:00 | 
| 2008-12-31 00:00:00 | 
| 2009-01-04 00:00:00 | 
| 2009-01-05 00:00:00 | 
+---------------------+
7 rows in set (0.00 sec)

Only 7 rows are returned, while we should have gotten 10.

It turns out that MySQL’s WEEK() function can operate in eight different ways. From the manual:

WEEK(date[,mode])

This function returns the week number for date. The two-argument form of WEEK() allows you to specify whether the week starts on Sunday or Monday and whether the return value should be in the range from 0 to 53 or from 1 to 53. If the mode argument is omitted, the value of the default_week_format system variable is used.

The following table describes how the mode argument works.

+--------------------------------------------------------------------+
| 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 what we really wanted was the WEEK(column, 3); mode:

mysql> SELECT start, WEEK(start, 3) FROM test;
+---------------------+----------------+
| start               | WEEK(start, 3) |
+---------------------+----------------+
| 2008-12-27 00:00:00 |             52 | 
| 2008-12-28 00:00:00 |             52 | 
| 2008-12-29 00:00:00 |              1 | 
| 2008-12-30 00:00:00 |              1 | 
| 2008-12-31 00:00:00 |              1 | 
| 2009-01-01 00:00:00 |              1 | 
| 2009-01-02 00:00:00 |              1 | 
| 2009-01-03 00:00:00 |              1 | 
| 2009-01-04 00:00:00 |              1 | 
| 2009-01-05 00:00:00 |              2 | 
+---------------------+----------------+
10 rows in set (0.00 sec)

So, take care when using MySQL’s WEEK() function, and always make sure to read the manual on all the functions you use at least once.

Blog

MySQL WEEK() function weirdness