What mode for MySQL WEEK() complies with ISO 8601

mysql iso8601
steampowered picture steampowered · Aug 3, 2012 · Viewed 16k times · Source

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?

Answer

Michael Madsen picture Michael Madsen · Aug 4, 2012

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.

Related questions

Format date in MySQL SELECT as ISO 8601

I'm trying to grab the date from my database in a standard timestamp and display it as ISO 8601. I'm unable to easily do it in PHP so I'm trying to do it in my SELECT statement. This is what I …

Read More
MySQL insert to DATETIME: is it safe to use ISO::8601 format?

In our project we use Zend Framework Model generator, which produces something like this to set the properties that are stored in DB (MySQL) as DATETIME fields: public function setObjectDatetime($data) { if (! $data instanceof Zend_Date) { ... some conversion code ... } $this-&…

Read More
How do I store an UTC ISO8601 date in a MySQL database?

I have thousands of dates in the following format: 2011-10-02T23:25:42Z (aka ISO 8601 in UTC) What MySQL data type should I use for storing such a ISO8601 date in a MySQL database? E.g. Datetime, timestamp or something …

Read More