sql - Oracle, split a time duration row by one hour period

Question

I'm working on oracle db and I'm not quite good at Oracle. I'm trying to split a row by one hour period.

For example, if a time row has given as below,

Start_time(yyyy/mm/dd hh24:mi:ss) | End_time(yyyy/mm/dd hh24:mi:ss)

2013/09/01 09:30:00         2013/09/01 11:38:59

The result I want to see like this:

2013/09/01 09:30:00         2013/09/01 09:59:59  
2013/09/01 10:00:00         2013/09/01 10:59:59
2013/09/01 11:00:00         2013/09/01 11:38:59

I've been searched how to do it but I couldn't find one.
But I guess It can be done by using 'CONNECT BY'.
Any help will be great. Thanks in advance.

Answer 1

I have bulit a basic query, you can work around it and get what you want.

select greatest(Start_time, trunc(Start_time+(level-1)/24, 'hh24')), 
least(End_time, trunc(Start_time+(level)/24, 'hh24'))
from log_table 
connect by level <= floor((dt1-dt2)*24)+1;

Example at sqlfiddle:

http://sqlfiddle.com/#!4/82625/29