解决mysql 和java Calendar 中第几周不一致的问题
mysqljava

我们在业务场景中,有的时候需要做范围内的聚合统计,比如按月按周。由于数据库的数据不一定是连续的,比如中间有个时间段的没有数据的,我们group出来的数据就会断,这个时候,我们需要借助程序,计算出所有的时间段。

一般的来说,我们可以用mysql的data_format函数处理就可以了。

select date_format(now(),'%Y-%U');

得到结果:2020-10,也就是2020年的第10周

但是,mysql 存在一个问题 ,如果当年的1月1日不是week 的第一天,那么第一周的周数是0,这个跟java 的Calendar的处理是不一样的,Calendar是从1开始的。

解决方案:

我们去判断当年的1月1日是否是设置的周第一天,如果是,正常计算,如果不是,需要减1.

public static boolean isFirstWeekFull(Calendar calendar){
    calendar.set(Calendar.MONTH,0);
    calendar.set(Calendar.DATE,1);
   return Objects.equals(calendar.get(Calendar.DAY_OF_WEEK),calendar.getFirstDayOfWeek());
}

下面是计算时间段内的所有日,月,周,年的工具函数。

public static List<TripSummaryRangeDTO> allYears(Date start,Date end){
    Calendar startCalender = Calendar.getInstance();
    startCalender.setTime(start);
    startCalender.set(Calendar.MONTH,0);
    startCalender.set(Calendar.DATE,1);

    Calendar endCalender = Calendar.getInstance();
    endCalender.setTime(end);
    endCalender.set(Calendar.MONTH,11);
    endCalender.set(Calendar.DATE,1);

    List<TripSummaryRangeDTO> years = Lists.newArrayList();
    while (!startCalender.after(endCalender)) {
        Calendar calendar = Calendar.getInstance();
        calendar.setTime(startCalender.getTime());
        TripSummaryRangeDTO item = new TripSummaryRangeDTO();
        item.setName( String.valueOf(calendar.getWeekYear()) );
        calendar.set(Calendar.MONTH,0);
        calendar.set(Calendar.DATE,1);
        item.setStart(calendar.getTime());
        calendar.set(Calendar.MONTH,11);
        calendar.set(Calendar.DAY_OF_MONTH, calendar.getActualMaximum(Calendar.DAY_OF_MONTH));
        item.setEnd(calendar.getTime());
        years.add(item);
        startCalender.add(Calendar.YEAR, 1);
    }
    return years;
}

public static List<TripSummaryRangeDTO> allMonths(Date start,Date end){
    SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM");
    Calendar startCalender = Calendar.getInstance();
    startCalender.setTime(start);
    startCalender.set(Calendar.DATE,1);

    Calendar endCalender = Calendar.getInstance();
    endCalender.setTime(end);
    endCalender.set(Calendar.DAY_OF_MONTH, endCalender.getActualMaximum(Calendar.DAY_OF_MONTH));

    List<TripSummaryRangeDTO> years = Lists.newArrayList();
    while (!startCalender.after(endCalender)) {

        Calendar calendar = Calendar.getInstance();
        calendar.setTime(startCalender.getTime());
        TripSummaryRangeDTO item = new TripSummaryRangeDTO();
        item.setName(simpleDateFormat.format(calendar.getTime()));
        calendar.set(Calendar.DATE,1);
        item.setStart(calendar.getTime());
        calendar.set(Calendar.DAY_OF_MONTH, calendar.getActualMaximum(Calendar.DAY_OF_MONTH));
        item.setEnd(calendar.getTime());
        years.add(item);

        startCalender.add(Calendar.MONTH, 1);
    }
    return years;
}

public static List<TripSummaryRangeDTO> allWeeks(Date start,Date end){

    Calendar startCalender = Calendar.getInstance();
    startCalender.setTime(start);
    startCalender.setFirstDayOfWeek(Calendar.MONDAY);
    startCalender.add(Calendar.DATE,1-startCalender.get(Calendar.DAY_OF_WEEK));

    Calendar endCalender = Calendar.getInstance();
    endCalender.setTime(end);
    endCalender.setFirstDayOfWeek(Calendar.MONDAY);
    endCalender.add(Calendar.DATE,7-endCalender.get(Calendar.DAY_OF_WEEK));

    List<TripSummaryRangeDTO> weeks = Lists.newArrayList();
    while (!startCalender.after(endCalender)) {

        Calendar calendar = Calendar.getInstance();
        calendar.setTime(startCalender.getTime());
        TripSummaryRangeDTO item = new TripSummaryRangeDTO();
        int weekNum = calendar.get(Calendar.WEEK_OF_YEAR);
        if(!isFirstWeekFull(calendar)){
            weekNum --;
        }
        item.setName(calendar.getWeekYear()+"-"+ weekNum );
        item.setStart(calendar.getTime());
        calendar.add(Calendar.DATE,6);
        item.setEnd(calendar.getTime());
        weeks.add(item);

        startCalender.add(Calendar.DATE, 7);
    }
    return weeks;
}

public static List<TripSummaryRangeDTO> allDays(Date start,Date end){
    SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd");
    Calendar startCalender = Calendar.getInstance();
    startCalender.setTime(start);

    Calendar endCalender = Calendar.getInstance();
    endCalender.setTime(end);

    List<TripSummaryRangeDTO> items = Lists.newArrayList();
    while (!startCalender.after(endCalender)) {

        Calendar calendar = Calendar.getInstance();
        calendar.setTime(startCalender.getTime());
        TripSummaryRangeDTO item = new TripSummaryRangeDTO();
        item.setName(simpleDateFormat.format(calendar.getTime()));
        item.setStart(calendar.getTime());
        item.setEnd(calendar.getTime());
        items.add(item);

        startCalender.add(Calendar.DATE, 1);
    }
    return items;
}

public static boolean isFirstWeekFull(Calendar calendar){
 Calendar calendar1 = Calendar.getInstance();
 calendar1.setTime(calendar.getTime());
 calendar1.set(Calendar.MONTH,0);
 calendar1.set(Calendar.DATE,1);
return Objects.equals(calendar.get(Calendar.DAY_OF_WEEK),calendar1.getFirstDayOfWeek());
}



暂无评论