如何保证joda-time|Calendar|MySQL计算年周的一致性

在系统开发过程中，经常会遇到按周维度进行信息统计的情形，但是如果方法使用不当可能导致计算的周信息不匹配，要保证通过joda-time、java.util.Calendar、MySQL三种方式获取周的结果一致，关键是要统三种方式对周的定义。

1. joda-time

joda-time是ISO8601标准的一个实现。ISO8601中对周的定义如下：

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). 28 December is always in the last week of its year.

如果1月1日是星期一、星期二、星期三、星期四，那么它是该年的第一周。如果1月1日是星期五、星期六、星期日，它是前一年的第52周或第53周（没有第00周）。12月28日永远在该年的最后一周。

The week number can be described by counting the Thursdays: week 12 contains the 12th Thursday of the year.

周数可以通过计算星期四的数量来描述：第12周包含一年中的第12个星期四。

The ISO week-numbering year starts at the first day (Monday) of week 01 and ends at the Sunday before the new ISO year (hence without overlap or gap). It consists of 52 or 53 full weeks. The first ISO week of a year may have up to three days that are actually in the Gregorian calendar year that is ending; if three, they are Monday, Tuesday and Wednesday. Similarly, the last ISO week of a year may have up to three days that are actually in the Gregorian calendar year that is starting; if three, they are Friday, Saturday, and Sunday. The Thursday of each ISO week is always in the Gregorian calendar year denoted by the ISO week-numbering year.

ISO周编号年从第01周的第一天(星期一)开始，在新的ISO年之前的星期日结束(因此没有重叠或缺口)。
它包括52或53个完整的星期。一年中的第一个ISO周可能有多达三天的时间实际上是在即将结束的公历年；如果是三天，分别是星期一、星期二和星期三。同样，一年的最后一个ISO周可能有多达三天实际上是在即将开始的下一个公历年；如果有三天，分别是星期五、星期六和星期日。每个ISO周的星期四总是在公历年，用ISO周号表示。

总结，ISO周编号年从01周开始，一年有周数01-53；每周开始的第一天是星期一；当年的每个周必须满足一周至少有四天（主要针对开始、结束周）条件。

	@Test
    public void dateTime() {
        DateTime dt20181231 = new DateTime(2018, 12, 31, 0, 0);
        System.out.println(dt20181231.getWeekyear() + ":" + dt20181231.getWeekOfWeekyear());
        DateTime dt20190101 = new DateTime(2019, 1, 1, 0, 0);
        System.out.println(dt20190101.getWeekyear() + ":" + dt20190101.getWeekOfWeekyear());
    }

2018-12-31和2019-01-01都对应2019年的第一周。

2. java.util.Calendar

Java默认使用的是Gregorian calendar。所以在获取周信息的时候设置周的第一天为星期一、一周至少有四天的条件就可以啦。详见代码：

    @Test
    public void calendar() {
        Calendar dt20181231 = Calendar.getInstance();
        // 第一周至少有四天
        dt20181231.setMinimalDaysInFirstWeek(4);
        // 周的第一天是星期一
        dt20181231.setFirstDayOfWeek(Calendar.MONDAY);
        dt20181231.set(2018, 11, 31, 0, 0);
        System.out.println(dt20181231.getWeekYear() + ":" + dt20181231.get(Calendar.WEEK_OF_YEAR));

        Calendar dt20190101 = Calendar.getInstance();
        dt20190101.setMinimalDaysInFirstWeek(4);
        dt20181231.setFirstDayOfWeek(Calendar.MONDAY);
        dt20190101.set(2019, 0, 1, 0, 0);
        System.out.println(dt20190101.getWeekYear() + ":" + dt20190101.get(Calendar.WEEK_OF_YEAR));
    }

2018-12-31和2019-01-01都对应2019年的第一周。

3. MySQL

MySQL 的 YEARWEEK 是获取年份和周数的一个函数，函数形式为 YEARWEEK(date[,mode])，其中ISO8601对应的mode为 3 。
即 First day of week is Monday, Week Range is 1-53 and Week 1 is the first week with more than 3 days this year.

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

-- 其结果为：201901 
SELECT YEARWEEK('2018-12-31', 3) FROM DUAL;
SELECT YEARWEEK('2019-01-01', 3) FROM DUAL;

参考

ISO 8601
Gregorian calendar
ISO week date
ISO8601 Java calendar system
MySQL WEEK（）的哪种模式符合ISO 8601
What mode for MySQL WEEK() complies with ISO 8601
Java关于周跨年的周数计算