获取两个时间区间季度Q半年H年Y

前言

报表需要获取当前时间下的区间季度,不是常规的Q1，Q2这种，网上想找类似例子没找到，索性自己写一个。
举例

        log.info(getQuarterByDate("2021-2", "2022-2", 1).toString());
        log.info(getQuarterByDate("2021-2", "2021-2", 1).toString());
        log.info(getQuarterByDate("2021-4", "2021-8", 1).toString());
        log.info(getQuarterByDate("2021-8", "2021-9", 1).toString());
        log.info(getQuarterByDate("2021-3", "2022-3", 1).toString());
        log.info(getQuarterByDate("2021-2", "2022-2", 2).toString());
        log.info(getQuarterByDate("2021-2", "2021-2", 2).toString());
        log.info(getQuarterByDate("2021-4", "2021-8", 2).toString());
        log.info(getQuarterByDate("2021-8", "2021-9", 2).toString());
        log.info(getQuarterByDate("2021-3", "2022-3", 2).toString());
        log.info(getQuarterByDate("2021-2", "2022-2", 3).toString());
        log.info(getQuarterByDate("2021-2", "2021-2", 3).toString());
        log.info(getQuarterByDate("2021-4", "2027-8", 3).toString());
        log.info(getQuarterByDate("2021-8", "2021-9", 3).toString());
        log.info(getQuarterByDate("2021-3", "2022-3", 3).toString());

==>>输出为
不足一个季度按照开始时间到当前季度结束时间为准，同理半年和年均这样

[2021-2~3, 2021-Q2, 2021-Q3, 2021-Q4, 2022-1~2]
[2021-2]
[2021-Q2, 2021-7~8]
[2021-8~9]
[2021-3, 2021-Q2, 2021-Q3, 2021-Q4, 2022-Q1]
[2021-H1, 2021-H2, 2022-H1]
[2021-H1]
[2021-H1, 2021-H2]
[2021-H2]
[2021-H1, 2021-H2, 2022-H1]
[2021, 2022]
[2021]
[2021, 2022, 2023, 2024, 2025, 2026, 2027]
[2021]
[2021, 2022]

工具类

    /**
     * 获取两个时间 的 区间季度
     * @param startTime 开始时间
     * @param endTime 结束时间
     * @param type 查询类型 1 季度 2半年 3 年
     * @return
     * @Author HezhezhiyuLe
     * @throws ParseException
     */
    public static List<String> getQuarterByDate(String startTime, String endTime, Integer type) throws ParseException {
        //返回语句数组
        List<String> quartersStr = new ArrayList();
        DateFormat df = new SimpleDateFormat("yyyy-MM");
        //获取开始结束的日历类
        Calendar cal = Calendar.getInstance();
        cal.setTime(df.parse(startTime));
        Calendar cal1 = Calendar.getInstance();
        cal1.setTime(df.parse(endTime));

        int startMonth = 0;
        //获取所有不重复月份对象数据
        List<TestDate> arr1 = new ArrayList<>();
        //比较时间
        while (cal1.getTime().after(cal.getTime()) || cal1.getTime().equals(cal.getTime())) {
            TestDate testDate = new TestDate();
            //程序中月份11月封顶
            startMonth = cal.get(Calendar.MONTH);
            testDate.setMonth(startMonth);
            testDate.setYear(cal.get(Calendar.YEAR));
            switch (startMonth) {
                case 0:
                case 1:
                case 2:
                    testDate.setQuarter(1);
                    break;
                case 3:
                case 4:
                case 5:
                    testDate.setQuarter(2);
                    testDate.setHalfyear(1);
                    break;
                case 6:
                case 7:
                case 8:
                    testDate.setQuarter(3);
                    break;
                case 9:
                case 10:
                case 11:
                    testDate.setQuarter(4);
                    testDate.setHalfyear(6);
                    break;
            }
            arr1.add(testDate);
            //递增一月
            cal.add(Calendar.MONTH, 1);
        }
        // 年 季度 月 表述关系 选择有序MAP
        Map<Integer, Map<Integer, List<TestDate>>> countsYears = new TreeMap<>();
        //按季度分对象
        for (TestDate testDate : arr1) {
            //获取当前月所属季度
            int quarter = testDate.getQuarter();
            int year = testDate.getYear();
            //存储 季度 月
            Map<Integer, List<TestDate>> YMQ = countsYears.get(year);
            if (YMQ == null) {
                // 季度月
                Map<Integer, List<TestDate>> MQ = new TreeMap<>();
                //单月
                List<TestDate> arr = new ArrayList<>();
                arr.add(testDate);
                MQ.put(quarter, arr);
                countsYears.put(year, MQ);
            } else {
                List<TestDate> testDates = YMQ.get(quarter);
                if (testDates == null) {
                    List<TestDate> arr = new ArrayList<>();
                    arr.add(testDate);
                    YMQ.put(quarter, arr);
                } else {
                    testDates.add(testDate);
                    YMQ.put(quarter, testDates);
                }
            }

        }
        //遍历当前年份 不重复
        for (Integer year : countsYears.keySet()) {
            //获得当前年的所有季度月
            Map<Integer, List<TestDate>> quarterMonths = countsYears.get(year);
 		   if(type==3){
                quartersStr.add(year);
                continue;
            }
            for (Integer quarter : quarterMonths.keySet()) {
                //获取当前季度 月份数组
                List<TestDate> testDates = quarterMonths.get(quarter);
                if (testDates == null) {
                    continue;
                }
                //当前月 不重复
                TestDate testDate = testDates.get(0);

                //替换为12进制
                int nowMonth = testDate.getMonth() + 1;
                switch (type){
                    //季度算法
                    case 1:
                        //满3个月算一季度 Q1
                        if (testDates.size() == 3) {
                            quartersStr.add(year + "-Q" + quarter);
                        } else if (testDates.size() == 1) {
                            //只有一个月算当月 1
                            quartersStr.add(year + "-" + nowMonth);
                        } else if (testDates.size() == 2) {
                            //一个季度中有两月算区间 2-3
                            quartersStr.add(year + "-" + nowMonth + "~" + (testDates.get(1).getMonth() + 1));
                        }
                        break;
                    // 半年算法
                    case 2:
                        //不添加重复半年 上半年H1 下半年H2
                        if (quarter<=2) {
                            String halfYearStr=year + "-H1";
                            if(!quartersStr.contains(halfYearStr)){
                                quartersStr.add(halfYearStr);
                            }

                        } else if (quarter>2) {
                            String halfYearStr=year + "-H2";
                            if(!quartersStr.contains(halfYearStr)){
                                quartersStr.add(halfYearStr);
                            }
                        }
                    break;
                }
            }
         
        }
        //返回组合语句 2021-2~3, 2021-Q2, 2021-Q3, 2021-Q4, 2022-1~2
        return quartersStr;
    }

类

package cn.ways.app.product.util;

public class TestDate {
    //月
    private int month;
    //季
    private int quarter;
    //年
    private int year;
    //半年
    private int halfyear;

    public int getMonth() {
        return month;
    }

    public void setMonth(int month) {
        this.month = month;
    }

    public int getQuarter() {
        return quarter;
    }

    public void setQuarter(int quarter) {
        this.quarter = quarter;
    }

    public int getYear() {
        return year;
    }

    public void setYear(int year) {
        this.year = year;
    }

    public int getHalfyear() {
        return halfyear;
    }

    public void setHalfyear(int halfyear) {
        this.halfyear = halfyear;
    }

    @Override
    public String toString() {
        return "TestDate{" +
                "month=" + month +
                ", quarter=" + quarter +
                ", year=" + year +
                ", halfyear=" + halfyear +
                '}';
    }
}

SQL举例

 		<isEqual property="dateType" compareValue="quarter">
			select case 
			         when counts = 3 then year||'-Q'||quarter
			         when counts = 1 then year||'-'||month
			         else year||' '||minMonth||'-'||maxmonth
			       end showTitle,
			       dense_rank() over(order by YEAR,quarter) rn,
			       a.*
			from (
			  select YM, YEAR, MONTH, QUARTER,
			         count(1) over(partition by YEAR, QUARTER) counts,
			         max(month) over(partition by YEAR, QUARTER) maxMonth,
			         min(month) over(partition by YEAR, QUARTER) minMonth
			  from (
			      select distinct year||lpad(month,2,0) ym,year,month,
			             case 
			                when month between 1 and 3 then 1
			                when month between 4 and 6 then 2
			                when month between 7 and 9 then 3
			                when month between 10 and 12 then 4
			              end quarter
			      from dm_date
			      where year||lpad(month,2,0) between $beginDate$ and $endDate$
			  )
			) a
		</isEqual>

附图：珠峰