What mode for MySQL WEEK() complies with ISO 8601

Question

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?

Columelliform

+1 for a search-friendliness (I've googled for "mysql week ISO-8601" and this question was a first result) – Paper

Isn't it: "with 4 or more days this year": dev.mysql.com/doc/refman/8.0/en/… – Camiecamila

Answer 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.

Per Wikipedia:

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.

Vernettaverneuil

To verify that this answer is correct, refer to the official definitions of "calendar week" and "calendar week number" in ISO 8601 sections 2.2.8 and 2.2.10. To clarify why there is no week 0, "calendar week number" is defined as an "ordinal number", and ordinal numbers begin at 1. – Thoroughpaced

I read with 3 days this year instead of with more than 3 days this year at wikipedia. Finally I figured it out. Thanks for this well explained answer! – Polyhedron

Still think '3' should be the default mode, as that corresponds with the calendar that most of us use. For some nerdy reason it is a mode which allows 'week 0' and I have to remind myself of this and Google the modes just to avoid potential catastrophe for one of my clients. – Aguste

Answer 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.

Acrobatic

To make this ISO-8601 compliant it would have to have the format '%x-W%v' or '%xW%v'. – Sprite

To tie this to the other answers, MySQL docs says %v is mode 3: %v Week (01..53), where Monday is the first day of the week; WEEK() mode 3; used with %x – Schizogony

Answer 3

The answer for ISO 8601 is mode 3:

SELECT week(your_date_column, 3) FROM your_table

Bim

Answer 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)

Hydrophane

I was interested to read your post, but I can't read french. Stack Overflow seems to have a preference for English – Columelliform