안녕하세요
생각보다 너무 힘든거 같아서 이렇게 문의 드립니다.
다음과 같이 테이블이 시작시간 종료시간 의 초단위 데이타가 있는데
비어 있는 시간을 초단위 기준 시작 ~ 종료로 시간을 구해야 하는데 도통 답이 않나옵니다.
고수 여러분의 많은 도움 부탁드립니다.
감사합니다.
SELECT to_Date ('2014-11-20 04:24:53','YYYY-MM-DD HH24:MI:SS') sdt ,
to_date('2014-11-20 04:25:04','YYYY-MM-DD HH24:MI:SS') edt
FROM dual
UNION ALL
SELECT to_Date ('2014-11-20 04:25:05','YYYY-MM-DD HH24:MI:SS'),
to_date('2014-11-20 04:25:14','YYYY-MM-DD HH24:MI:SS')
FROM dual
UNION ALL
SELECT to_Date ('2014-11-20 04:26:28','YYYY-MM-DD HH24:MI:SS'),
to_date('2014-11-20 04:27:00','YYYY-MM-DD HH24:MI:SS')
FROM dual
UNION ALL
SELECT to_Date ('2014-11-20 04:27:01','YYYY-MM-DD HH24:MI:SS'),
to_date('2014-11-20 04:27:12','YYYY-MM-DD HH24:MI:SS')
FROM dual
UNION ALL
SELECT to_Date ('2014-11-20 04:27:13','YYYY-MM-DD HH24:MI:SS'),
to_date('2014-11-20 04:27:26','YYYY-MM-DD HH24:MI:SS')
FROM dual
UNION ALL
SELECT to_Date ('2014-11-20 04:27:27','YYYY-MM-DD HH24:MI:SS'),
to_date('2014-11-20 04:27:37','YYYY-MM-DD HH24:MI:SS')
FROM dual
UNION ALL
SELECT to_Date ('2014-11-20 04:27:38','YYYY-MM-DD HH24:MI:SS'),
to_date('2014-11-20 04:27:48','YYYY-MM-DD HH24:MI:SS')
FROM dual
UNION ALL
SELECT to_Date ('2014-11-20 04:27:49','YYYY-MM-DD HH24:MI:SS'),
to_date('2014-11-20 04:28:18','YYYY-MM-DD HH24:MI:SS')
FROM dual
UNION ALL
SELECT to_Date ('2014-11-20 04:28:19','YYYY-MM-DD HH24:MI:SS'),
to_date('2014-11-20 04:28:30','YYYY-MM-DD HH24:MI:SS')
FROM dual
UNION ALL
SELECT to_Date ('2014-11-20 04:28:31','YYYY-MM-DD HH24:MI:SS'),
to_date('2014-11-20 04:29:03','YYYY-MM-DD HH24:MI:SS')
FROM dual
UNION ALL
SELECT to_Date ('2014-11-20 04:29:04','YYYY-MM-DD HH24:MI:SS'),
to_date('2014-11-20 04:29:15','YYYY-MM-DD HH24:MI:SS')
FROM dual
UNION ALL
SELECT to_Date ('2014-11-20 04:29:16','YYYY-MM-DD HH24:MI:SS'),
to_date('2014-11-20 04:29:28','YYYY-MM-DD HH24:MI:SS')
FROM dual
UNION ALL
SELECT to_Date ('2014-11-20 04:29:29','YYYY-MM-DD HH24:MI:SS'),
to_date('2014-11-20 04:29:42','YYYY-MM-DD HH24:MI:SS')
FROM dual
UNION ALL
SELECT to_Date ('2014-11-20 04:29:43','YYYY-MM-DD HH24:MI:SS'),
to_date('2014-11-20 04:29:55','YYYY-MM-DD HH24:MI:SS')
FROM dual
UNION ALL
SELECT to_Date ('2014-11-20 04:29:56','YYYY-MM-DD HH24:MI:SS'),
to_date('2014-11-20 04:29:59','YYYY-MM-DD HH24:MI:SS')
FROM dual
UNION ALL
SELECT to_Date ('2014-11-20 03:30:00','YYYY-MM-DD HH24:MI:SS'),
to_date('2014-11-20 03:30:10','YYYY-MM-DD HH24:MI:SS')
FROM dual
UNION ALL
SELECT to_Date ('2014-11-20 03:30:11','YYYY-MM-DD HH24:MI:SS'),
to_date('2014-11-20 03:30:23','YYYY-MM-DD HH24:MI:SS')
FROM dual
UNION ALL
SELECT to_Date ('2014-11-20 03:30:24','YYYY-MM-DD HH24:MI:SS'),
to_date('2014-11-20 03:30:34','YYYY-MM-DD HH24:MI:SS')
FROM dual
UNION ALL
SELECT to_Date ('2014-11-20 03:30:35','YYYY-MM-DD HH24:MI:SS'),
to_date('2014-11-20 03:30:48','YYYY-MM-DD HH24:MI:SS')
FROM dual
UNION ALL
SELECT to_Date ('2014-11-20 03:30:49','YYYY-MM-DD HH24:MI:SS'),
to_date('2014-11-20 03:31:00','YYYY-MM-DD HH24:MI:SS')
FROM dual
UNION ALL
SELECT to_Date ('2014-11-20 03:31:01','YYYY-MM-DD HH24:MI:SS'),
to_date('2014-11-20 03:31:12','YYYY-MM-DD HH24:MI:SS')
FROM dual
UNION ALL
SELECT to_Date ('2014-11-20 03:31:38','YYYY-MM-DD HH24:MI:SS'),
to_date('2014-11-20 03:37:53','YYYY-MM-DD HH24:MI:SS')
FROM dual
UNION ALL
SELECT to_Date ('2014-11-20 03:37:54','YYYY-MM-DD HH24:MI:SS'),
to_date('2014-11-20 03:38:08','YYYY-MM-DD HH24:MI:SS')
FROM dual
UNION ALL
SELECT to_Date ('2014-11-20 03:38:09','YYYY-MM-DD HH24:MI:SS'),
to_date('2014-11-20 03:38:23','YYYY-MM-DD HH24:MI:SS')
FROM dual
UNION ALL
SELECT to_Date ('2014-11-20 03:38:24','YYYY-MM-DD HH24:MI:SS'),
to_date('2014-11-20 03:38:40','YYYY-MM-DD HH24:MI:SS')
FROM dual
UNION ALL
SELECT to_Date ('2014-11-20 03:38:41','YYYY-MM-DD HH24:MI:SS'),
to_date('2014-11-20 03:43:50','YYYY-MM-DD HH24:MI:SS')
FROM dual
UNION ALL
SELECT to_Date ('2014-11-20 03:43:51','YYYY-MM-DD HH24:MI:SS'),
to_date('2014-11-20 04:06:37','YYYY-MM-DD HH24:MI:SS')
FROM dual
UNION ALL
SELECT to_Date ('2014-11-20 04:06:38','YYYY-MM-DD HH24:MI:SS'),
to_date('2014-11-20 04:06:51','YYYY-MM-DD HH24:MI:SS')
FROM dual
UNION ALL
SELECT to_Date ('2014-11-20 04:06:52','YYYY-MM-DD HH24:MI:SS'),
to_date('2014-11-20 04:07:06','YYYY-MM-DD HH24:MI:SS')
FROM dual
UNION ALL
SELECT to_Date ('2014-11-20 04:07:07','YYYY-MM-DD HH24:MI:SS'),
to_date('2014-11-20 04:07:27','YYYY-MM-DD HH24:MI:SS')
FROM dual
UNION ALL
SELECT to_Date ('2014-11-20 04:07:28','YYYY-MM-DD HH24:MI:SS'),
to_date('2014-11-20 04:07:40','YYYY-MM-DD HH24:MI:SS')
FROM dual
UNION ALL
SELECT to_Date ('2014-11-20 04:07:41','YYYY-MM-DD HH24:MI:SS'),
to_date('2014-11-20 04:08:08','YYYY-MM-DD HH24:MI:SS')
FROM dual
UNION ALL
SELECT to_Date ('2014-11-20 04:08:09','YYYY-MM-DD HH24:MI:SS'),
to_date('2014-11-20 04:08:19','YYYY-MM-DD HH24:MI:SS')
FROM dual
UNION ALL
SELECT to_Date ('2014-11-20 04:08:20','YYYY-MM-DD HH24:MI:SS'),
to_date('2014-11-20 04:08:33','YYYY-MM-DD HH24:MI:SS')
FROM dual
UNION ALL
SELECT to_Date ('2014-11-20 04:08:34','YYYY-MM-DD HH24:MI:SS'),
to_date('2014-11-20 04:08:44','YYYY-MM-DD HH24:MI:SS')
FROM dual
UNION ALL
SELECT to_Date ('2014-11-20 04:08:45','YYYY-MM-DD HH24:MI:SS'),
to_date('2014-11-20 04:08:55','YYYY-MM-DD HH24:MI:SS')
FROM dual
UNION ALL
SELECT to_Date ('2014-11-20 04:08:56','YYYY-MM-DD HH24:MI:SS'),
to_date('2014-11-20 04:09:08','YYYY-MM-DD HH24:MI:SS')
FROM dual
UNION ALL
SELECT to_Date ('2014-11-20 04:09:09','YYYY-MM-DD HH24:MI:SS'),
to_date('2014-11-20 04:09:23','YYYY-MM-DD HH24:MI:SS')
FROM dual
UNION ALL
SELECT to_Date ('2014-11-20 04:10:01','YYYY-MM-DD HH24:MI:SS'),
to_date('2014-11-20 04:10:11','YYYY-MM-DD HH24:MI:SS')
FROM dual
UNION ALL
SELECT to_Date ('2014-11-20 04:10:12','YYYY-MM-DD HH24:MI:SS'),
to_date('2014-11-20 04:10:23','YYYY-MM-DD HH24:MI:SS')
FROM dual
UNION ALL
SELECT to_Date ('2014-11-20 04:10:24','YYYY-MM-DD HH24:MI:SS'),
to_date('2014-11-20 04:10:36','YYYY-MM-DD HH24:MI:SS')
FROM dual
UNION ALL
SELECT to_Date ('2014-11-20 04:10:37','YYYY-MM-DD HH24:MI:SS'),
to_date('2014-11-20 04:10:50','YYYY-MM-DD HH24:MI:SS')
FROM dual
UNION ALL
SELECT to_Date ('2014-11-20 04:10:51','YYYY-MM-DD HH24:MI:SS'),
to_date('2014-11-20 04:11:02','YYYY-MM-DD HH24:MI:SS')
FROM dual
UNION ALL
SELECT to_Date ('2014-11-20 04:11:03','YYYY-MM-DD HH24:MI:SS'),
to_date('2014-11-20 04:11:16','YYYY-MM-DD HH24:MI:SS')
FROM dual
UNION ALL
SELECT to_Date ('2014-11-20 04:11:17','YYYY-MM-DD HH24:MI:SS'),
to_date('2014-11-20 04:24:41','YYYY-MM-DD HH24:MI:SS')
FROM dual
UNION ALL
SELECT to_Date ('2014-11-20 04:24:42','YYYY-MM-DD HH24:MI:SS'),
to_date('2014-11-20 04:24:52','YYYY-MM-DD HH24:MI:SS')
FROM dual
|