java和mysql的week base year格式化问题

Question

当某个星期跨年的时候，mysql的date_format()认为这个星期是属于前一年的

select date_format('2020-01-01', '%X-%V')

结果为

'2019-52'

如何创建一个DateTimeFormatter使格式化结果与mysql的'%X-%V'相同?

尝试过以下方法，并不一致

---

       final DateTimeFormatter formatter = new DateTimeFormatterBuilder()
                .parseCaseInsensitive()
                .appendValue(IsoFields.WEEK_BASED_YEAR, 4, 10, SignStyle.EXCEEDS_PAD)
                .appendLiteral("-")
                .appendValue(IsoFields.WEEK_OF_WEEK_BASED_YEAR, 2).toFormatter();

        System.out.println(LocalDate.parse("2020-01-01").format(formatter));//2020-01
        System.out.println(LocalDate.parse("2020-12-31").format(formatter));//2020-53
        System.out.println(LocalDate.parse("2019-01-01").format(formatter));//2019-01
        System.out.println(LocalDate.parse("2020-05-31").format(formatter));//2020-22

        System.out.println();
        final DateTimeFormatter formatter2 = DateTimeFormatter.ofPattern("YYYY-ww");

        System.out.println(LocalDate.parse("2020-01-01").format(formatter2));//2020-01
        System.out.println(LocalDate.parse("2020-12-31").format(formatter2));//2021-01
        System.out.println(LocalDate.parse("2019-01-01").format(formatter2));//2019-01
        System.out.println(LocalDate.parse("2020-05-31").format(formatter2));//2020-23

粗略看了下源码，似乎是得自己实现两个TemporalField
orz

Answer 1

通过java.time.temporal.WeekFields#of(java.time.DayOfWeek, int)方法可以方便地获得自定义规则的TemporalField实例。

两个参数：

• DayOfWeek firstDayOfWeek 指定一周从星期几开始
• int minimalDaysInFirstWeek 指定一年中的第一周至少需要的天数

调用 WeekFields.of(DayOfWeek.SUNDAY, 7)即可得到对应mysql5.7中'%X-%V'格式的规则

        final WeekFields weekFields = WeekFields.of(DayOfWeek.SUNDAY, 7);
        final DateTimeFormatter formatter = new DateTimeFormatterBuilder()
                .parseCaseInsensitive()
                .appendValue(weekFields.weekBasedYear(), 4, 10, SignStyle.EXCEEDS_PAD)
                .appendLiteral("-")
                .appendValue(weekFields.weekOfWeekBasedYear(), 2).toFormatter();

        Stream.iterate(LocalDate.parse("2017-12-29"), date -> date.plusDays(1)).limit(10).forEach(date -> {
                    System.out.printf("%-12s%-15s%10s\n",date, date.getDayOfWeek(), formatter.format(date));
                }
        );

输出

2017-12-29  FRIDAY            2017-52
2017-12-30  SATURDAY          2017-52
2017-12-31  SUNDAY            2017-53
2018-01-01  MONDAY            2017-53
2018-01-02  TUESDAY           2017-53
2018-01-03  WEDNESDAY         2017-53
2018-01-04  THURSDAY          2017-53
2018-01-05  FRIDAY            2017-53
2018-01-06  SATURDAY          2017-53
2018-01-07  SUNDAY            2018-01

mysql 5.7对照

select date_format('2017-12-29', '%X-%V'), 
date_format('2017-12-30', '%X-%V'), # 周六
date_format('2017-12-31', '%X-%V'), 
date_format('2018-01-01', '%X-%V'), # 周一
date_format('2018-01-02', '%X-%V'),
date_format('2018-01-03', '%X-%V'),
date_format('2018-01-04', '%X-%V'),
date_format('2018-01-05', '%X-%V'),
date_format('2018-01-06', '%X-%V'), # 周六
date_format('2018-01-07', '%X-%V')

输出

2017-52    2017-52    2017-53    2017-53    2017-53    2017-53    2017-53    2017-53    2017-53    2018-01

tips

WeekFields定义了两个常量实例:

• public static final WeekFields ISO = new WeekFields(DayOfWeek.MONDAY, 4) 由ISO-8601定义的规则，一周从星期一开始，第一周至少需要4天
• public static final WeekFields SUNDAY_START = WeekFields.of(DayOfWeek.SUNDAY, 1)欧洲国家和美国使用的规则。（This week definition is in use in the US and other European countries） 一周从星期天开始，第一周至少需要1天