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

通过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天