H5W3
当前位置:H5W3 > 其他技术问题 > 正文

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

当某个星期跨年的时候,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

回答:

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

本文地址:H5W3 » java和mysql的week base year格式化问题

评论 0

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址