Connect by 시간별로 나타내기

1분 간격

SELECT TO_DATE('20160510','YYYYMMDD') - (LEVEL/24/60*1-1) AS CHK_DATE 

FROM DUAL 

CONNECT BY LEVEL <= ((24*60)/1)

결과물

2016-05-10 23:59:00

2016-05-10 23:58:00

2016-05-10 23:57:00

.

.

2016-05-10 00:02:00

2016-05-10 00:01:00

2016-05-10 00:00:00

1시간 간격

SELECT TO_DATE('20160510','YYYYMMDD') - (LEVEL/24*1-1) AS CHK_DATE 

FROM DUAL 

CONNECT BY LEVEL <= ((24)/1);

결과물

2016-05-10 23:00:00

2016-05-10 22:00:00

2016-05-10 21:00:00

.

.

.

2016-05-10 02:00:00

2016-05-10 01:00:00

2016-05-10 00:00:00

1시간 간격 + 하루 이상

SELECT TO_DATE('20160501', 'YYYYMMDD') + (LEVEL-1)/24

FROM DUAL

CONNECT BY LEVEL <= (TO_DATE('20160510', 'YYYYMMDD') - TO_DATE('20160501', 'YYYYMMDD'))*(24);

결과물

2016-05-01 00:00:00

2016-05-01 01:00:00

2016-05-01 02:00:00

.

.

.

2016-05-09 21:00:00

2016-05-09 22:00:00

2016-05-09 23:00:00

혹시 궁금할까봐 적어놓음

1일 : 1

1시간 : 1*24 | 1/24

1분 : 1*24*60 | 1/24/60

1초 : 1*24*60 | 1/24/60/60

막 휘갈겨 썼으니 적당히 조절해서 써야지