What mode for MySQL WEEK() complies with ISO 8601

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.

Leave a Comment

Hata!: SQLSTATE[HY000] [1045] Access denied for user 'divattrend_liink'@'localhost' (using password: YES)