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;
sqlfiddle的示例:
Oracle,将持续时间行除以一小时
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;
sqlfiddle的示例: