What mode for MySQL WEEK() complies with ISO 8601
MysqlIso8601Mysql Problem Overview
What mode for MySQL's WEEK() function yields the ISO 8601 week of the year? Argument 2 of WEEK() sets the mode according to this chart:
+--------------------------------------------------------------------+
| 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 |
+--------------------------------------------------------------------+
Will one of these modes give the ISO 8601 week of the year?
Mysql Solutions
Solution 1 - Mysql
In ISO week numbering, Monday is the first day of the week, so that alone narrows it down to one of the odd-numbered modes.
> There are mutually equivalent descriptions of week 01: > >* the week with the year's first Thursday in it (the formal ISO definition), >* the week with 4 January in it, >* the first week with the majority (four or more) of its days in the starting year, and >* the week starting with the Monday in the period 29 December – 4 January.
The third of those descriptions matches "with more than 3 days this year" from the table above, so now we've narrowed it down to either 1 or 3.
Finally, still from Wikipedia (emphasis added):
> If 1 January is on a Monday, Tuesday, Wednesday or Thursday, it is in week 01. If 1 January is on a Friday, Saturday or Sunday, it is in week 52 or 53 of the previous year (there is no week 00).
Hence, the range must be 1-53, not 0-53. This in turn means the right mode is mode 3.
Solution 2 - Mysql
I know this question is old, but it is well SEO positioned :) So just to make the answer more complete - when displaying the year and week number, you can use this:
DATE_FORMAT(your_date_here, "%x-%v")
it will produce the iso week number for "%v" (1-53) and a correct year number for "%x".
Solution 3 - Mysql
For France you have to put in file /etc/mysql/my.cnf § [Mysqld] : default_week_format = 3
Notice : dont work for YEARWEEK, needs add mode = 3 SELECT YEARWEEK(CURDATE(), 3)
Pour la France vous devez modifier ou ajouter cette ligne dans le paragraphe [Mysqld] du fichier /etc/mysql/my.cnf : default_week_format = 3 Cela permet à la fonction WEEK de retourner les vraies numéros de semaines Française. Attention pour la fonction YEARWEEK il faut impérativement ajouter le mode à 3 SELECT YEARWEEK(CURDATE(), 3)
Solution 4 - Mysql
The answer for ISO 8601 is mode 3:
SELECT week(your_date_column, 3) FROM your_table