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?
Answers:
Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.
Method 1
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.
Method 2
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”.
Method 3
The answer for ISO 8601 is mode 3:
SELECT week(your_date_column, 3) FROM your_table
Method 4
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)
All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0