Oracle sql生成连续时间

数据表中有基于稳定频次（如5秒一次，10分钟一次）的流水数据，在页面中画曲线图时，若某段时间数据突然中断，此时若直接SELECT数据表则看不到中断的情况。正确处理方式应是基于连续时间关联该数据表查询。Oracle中可用 CONNECT BY生成连续时间。如以下SQL语句将生成连续的24小时：

SELECT TRUNC((TO_DATE('2018-01-22 00:00','yyyy-mm-dd hh24:mi')-(25-Rownum)/24),'hh') serialtime FROM DUAL CONNECT BY rownum <= 24

其他生成连续的年、月、周、日、分类似

public static void serialTime(int type,Map<String,Object> params){
        String arr[]=parseLayDate((String)params.get("startEndDate"));
        String startDate=arr[0];
        String endDate=arr[1];
        params.put("startDate",startDate);
        params.put("endDate",endDate);
        LocalDateTime start=null;
    	LocalDateTime end=null;
    	DateTimeFormatter formatter= DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm");
        try {      	
        	start = LocalDateTime.parse(startDate, formatter);
        	end = LocalDateTime.parse(endDate, formatter);         
        }catch (Exception e){
            e.printStackTrace();
        }
        String s=null;
        String truncFormat=null;
        String charFormat=null;
        long interval;       
        if (type==Constants.TIME_MINUTE) {//按分钟
        	interval=Duration.between(start, end).toMinutes();
            s="SELECT (TO_DATE('"+endDate+"','yyyy-mm-dd hh24:mi')-("+(interval+1)+"-Rownum)/1440) serialtime FROM DUAL CONNECT BY rownum <= "+interval;
            truncFormat="mi";
            charFormat="hh24:mi";
        }else if (type==Constants.TIME_HOUR) {//按小时 
        	start=start.withMinute(0);
        	if (end.getMinute()>0) {
        		end=end.withMinute(0).plusHours(1);
				endDate=end.format(formatter);
			}
        	interval=Duration.between(start, end).toHours();
            s="SELECT TRUNC((TO_DATE('"+endDate+"','yyyy-mm-dd hh24:mi')-("+(interval+1)+"-Rownum)/24),'hh') serialtime FROM DUAL CONNECT BY rownum <= "+interval;
            truncFormat="hh";
            charFormat="mm-dd hh24";
        }else if (type==Constants.TIME_DAY) {//按天
        	start=start.withHour(0).withMinute(0);
        	if (end.getHour() + end.getMinute()>0) {
				end=end.withHour(0).withMinute(0).plusDays(1);
				endDate=end.format(formatter);
			}
        	interval=Duration.between(start, end).toDays();
            s="SELECT TRUNC((TO_DATE('"+endDate+"','yyyy-mm-dd hh24:mi')-("+(interval+1)+"-Rownum)),'dd') serialtime FROM DUAL CONNECT BY rownum <= "+interval;
            truncFormat="dd";
            charFormat="mm-dd";
        }else if (type==Constants.TIME_WEEK) {//按周
        	start=start.withHour(0).withMinute(0);
        	if (end.getDayOfWeek().getValue()>1 || end.getHour() + end.getMinute()>0) {
        		end=end.withHour(0).withMinute(0).plusDays(14-end.getDayOfWeek().getValue());
				endDate=end.format(formatter);
			}
        	interval=Duration.between(start, end).toDays()/7;
            s="SELECT TRUNC((TO_DATE('"+endDate+"','yyyy-mm-dd hh24:mi')-("+(interval+1)+"-Rownum)*7),'iw') serialtime FROM DUAL CONNECT BY rownum <= "+interval;
            truncFormat="iw";
            charFormat="mm-dd";
        }else if (type==Constants.TIME_MONTH) {//按月
        	start=start.withDayOfMonth(1).withHour(0).withMinute(0);
        	if (end.getDayOfMonth()>1 || end.getHour() + end.getMinute()>0) {
        		end=end.withDayOfMonth(1).withHour(0).withMinute(0).plusMonths(1);
				endDate=end.format(formatter);
			}
        	interval=Duration.between(start, end).toDays()/14;
            s="SELECT DISTINCT TRUNC((TO_DATE('"+endDate+"','yyyy-mm-dd hh24:mi')-Rownum*14),'mm') serialtime FROM DUAL CONNECT BY rownum <= "+interval+" ORDER BY serialtime";
            truncFormat="mm";
            charFormat="yyyy-mm";
        }
        params.put("timeSql",s);
        params.put("truncFormat","'"+truncFormat+"'");
        params.put("charFormat","'"+charFormat+"'");
        System.out.println(s);
    }
	
	/**
     * 解析前端layDate开始结束日期
     * @param startEndDate
     * @return
     */
    private static String[] parseLayDate(String startEndDate){
        String arr[]=startEndDate.split(" - ");
        return arr;
    }