我们在业务场景中，有的时候需要做范围内的聚合统计，比如按月按周。由于数据库的数据不一定是连续的，比如中间有个时间段的没有数据的，我们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 calendar1 = Calendar.getInstance();
    calendar1.set(calendar.get(Calendar.YEAR), Calendar.JANUARY,1);
    return Objects.equals(calendar1.get(Calendar.DAY_OF_WEEK),calendar1.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());
}