시작일자 종료일자
2019-10-30 01:01:00 2019-10-30 01:03:10
2019-10-30 01:03:10 2019-10-30 01:13:40
2019-10-30 01:13:40 2019-10-30 02:03:10
2019-10-30 02:03:10 2019-10-30 02:23:54
시작 ~ 종료 시간 데이터로 10분 간격으로 데이터를 조회하려고 합니다.
ex )
2019-10-30 01:01:00
2019-10-30 01:11:00
2019-10-30 01:21:00
2019-10-30 01:31:00
2019-10-30 01:41:00
....
어떻게 해야될지 감이 안잡히네요.
아래 쿼리의 RESULT 값을 원하시는 건가요? WITH A AS( SELECT '2019-10-30 01:01:00' ST_TIME, '2019-10-30 01:03:10' ED_TIME FROM DUAL UNION ALL SELECT '2019-10-30 01:03:10' ST_TIME, '2019-10-30 01:13:40' ED_TIME FROM DUAL UNION ALL SELECT '2019-10-30 01:13:40' ST_TIME, '2019-10-30 02:03:10' ED_TIME FROM DUAL UNION ALL SELECT '2019-10-30 02:03:10' ST_TIME, '2019-10-30 02:23:54' ED_TIME FROM DUAL ) SELECT ED_TIME,ST_TIME,LEVEL*10 MINUTE ,ST_TIME+(LEVEL*10/24/60) RESULT FROM( SELECT MAX(TO_DATE(ED_TIME,'YYYY-MM-DD HH24:MI:SS')) ED_TIME ,MIN(TO_DATE(ST_TIME,'YYYY-MM-DD HH24:MI:SS')) ST_TIME ,FLOOR(((MAX(TO_DATE(ED_TIME,'YYYY-MM-DD HH24:MI:SS'))-MIN(TO_DATE(ST_TIME,'YYYY-MM-DD HH24:MI:SS')))*86400 /60)/10) CNT FROM A )A CONNECT BY LEVEL<=CNT
WITH t AS ( SELECT TO_DATE(sdt, 'yyyy-mm-dd hh24:mi:ss') sdt , TO_DATE(edt, 'yyyy-mm-dd hh24:mi:ss') edt FROM (SELECT '2019-10-30 01:01:00' sdt, '2019-10-30 01:03:10' edt FROM dual UNION ALL SELECT '2019-10-30 01:03:10', '2019-10-30 01:13:40' FROM dual UNION ALL SELECT '2019-10-30 01:13:40', '2019-10-30 02:03:10' FROM dual UNION ALL SELECT '2019-10-30 02:03:10', '2019-10-30 02:23:54' FROM dual ) ) SELECT s + (LEVEL - 1)/24/60*10 x FROM (SELECT MIN(sdt) s , MAX(edt) e FROM t ) CONNECT BY LEVEL <= (e - s) * 24 * 60 / 10 + 1 ;