MySQL WEEK()的哪种模式符合ISO 8601 [英] What mode for MySQL WEEK() complies with ISO 8601

问题描述

MySQL的 函数会产生一年中的ISO 8601周? WEEK()的参数2根据此图表设置模式:

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      |
+--------------------------------------------------------------------+

这些模式中的一种会在一年中的某个星期提供ISO 8601吗?

Will one of these modes give the ISO 8601 week of the year?

推荐答案

在ISO周编号中，星期一是一周中的第一天，因此仅凭它就可以将其范围缩小为奇数模式之一.

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.

每个Wikipedia :

第01周的描述彼此等效:

There are mutually equivalent descriptions of week 01:

• 其中有一年的第一个星期四的星期(正式的ISO定义)
• 其中有1月4日的一周，
• 第一年中拥有大部分(四天或以上)工作日的第一周，并且
• 从12月29日至1月4日的星期一开始的一周.

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

这些描述中的三分之一与上表中的今年超过3天"匹配，因此现在我们将其范围缩小到1或3.

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

如果1月1日是星期一，星期二，星期三或星期四，则在第01周.如果1月1日是星期五，星期六或星期日，则是在上一年的第52或53周(没有星期00).

因此，范围必须为1到53，而不是0到53.这又意味着正确的模式是模式3 .

Hence, the range must be 1-53, not 0-53. This in turn means the right mode is mode 3.

这篇关于MySQL WEEK()的哪种模式符合ISO 8601的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！