with t as (select 1001 id, to_date('20140725 00:00:00', 'yyyymmdd hh24:mi:ss') begintime, to_date('20140725 06:00:00', 'yyyymmdd hh24:mi:ss') endtime from dual union all select 1001 id, to_date('20140725 09:00:00', 'yyyymmdd hh24:mi:ss') begintime, to_date('20140725 12:30:00', 'yyyymmdd hh24:mi:ss') endtime from dual union all select 1001 id, to_date('20140725 15:00:00', 'yyyymmdd hh24:mi:ss') begintime, to_date('20140725 19:15:00', 'yyyymmdd hh24:mi:ss') endtime from dual) select id,to_char(t1,'yyyymmdd hh24:mi:ss')||'~'||to_char(t2,'yyyymmdd hh24:mi:ss') from (select id, time t1, lead(time) over(partition by id, rn order by time) t2 from (select id, rn, begintime + (level - 1) * 30 / 24 / 60 time from (select t.*, rownum rn from t) connect by prior begintime + (level - 1) * 30 / 24 / 60 <= endtime and prior rn = rn and prior dbms_random.value is not null union select id, rownum, endtime from t)) where t2 is not null;
with t as
(select 1001 id,
to_date('20140725 00:00:00', 'yyyymmdd hh24:mi:ss') begintime,
to_date('20140725 06:00:00', 'yyyymmdd hh24:mi:ss') endtime
from dual
union all
select 1001 id,
to_date('20140725 09:00:00', 'yyyymmdd hh24:mi:ss') begintime,
to_date('20140725 12:30:00', 'yyyymmdd hh24:mi:ss') endtime
from dual
union all
select 1001 id,
to_date('20140725 15:00:00', 'yyyymmdd hh24:mi:ss') begintime,
to_date('20140725 19:15:00', 'yyyymmdd hh24:mi:ss') endtime
from dual)
select id,to_char(t1,'yyyymmdd hh24:mi:ss')||'~'||to_char(t2,'yyyymmdd hh24:mi:ss')
from (select id,
time t1,
lead(time) over(partition by id, rn order by time) t2
from (select id, rn, begintime + (level - 1) * 30 / 24 / 60 time
from (select t.*, rownum rn from t)
connect by prior
begintime + (level - 1) * 30 / 24 / 60 <= endtime
and prior rn = rn
and prior dbms_random.value is not null
union
select id, rownum, endtime
from t))
where t2 is not null;