可以在sql中执行此操作.有两个技巧.第一个是生成一系列数字,您可以使用连接进行CTE.
第二个是整合正确的逻辑来扩展日期,同时保持开始和结束的正确时间.
以下是一个例子:
with n as (
select level n
from dual connect by level <= 20
),t as (
select 1 as id,to_date('01/01/2000 4','mm/dd/yyyy hh') as StartDate,to_date('01/03/2000 6','mm/dd/yyyy hh') as EndDate from dual union all
select 2 as id,to_date('01/04/2000 8',to_date('01/04/2000 12','mm/dd/yyyy hh') as EndDate from dual union all
select 3 as id,to_date('01/05/2000','mm/dd/yyyy') as StartDate,to_date('01/06/2000','mm/dd/yyyy') as EndDate from dual
)
select t.id,(case when n = 1 then StartDate
else trunc(StartDate + n - 1)
end) as StartDate,(case when trunc(StartDate + n - 1) = trunc(enddate)
then enddate
else trunc(StartDate + n)
end)
from t join
n
on StartDate + n - 1 <= EndDate
order by id,StartDate
Here它在sqlFiddle上.