另一种非递归cte方法可能是

SQL> with test (time_from, time_to) as
  2    (select to_date('09.08.2020 07:00', 'dd.mm.yyyy hh24:mi'),
  3            to_date('09.08.2020 14:00', 'dd.mm.yyyy hh24:mi')
  4            from dual union all
  5     select to_date('09.08.2020 16:00', 'dd.mm.yyyy hh24:mi'),
  6            to_date('09.08.2020 18:00', 'dd.mm.yyyy hh24:mi')
  7            from dual
  8    )
  9  select time_from + ((column_value - 1) * 60) / (24 * 60) time
 10  from test cross join
 11    table(cast(multiset(select level from dual
 12                        connect by level <= (time_to - time_from) * 24
 13                       ) as sys.odcinumberlist));

TIME
----------------
09.08.2020 07:00
09.08.2020 08:00
09.08.2020 09:00
09.08.2020 10:00
09.08.2020 11:00
09.08.2020 12:00
09.08.2020 13:00
09.08.2020 16:00
09.08.2020 17:00

9 rows selected.

SQL>

这些是有时间的日期-你会申请的 TO_CHAR 使用所需的格式掩码来显示它。

select to_char(time_from + ((column_value - 1) * 60) / (24 * 60), 'hh:mi am') time

结果是

TIME
--------
07:00 AM
08:00 AM
09:00 AM
10:00 AM
11:00 AM
12:00 PM
01:00 PM
04:00 PM
05:00 PM

9 rows selected.

如果要使用“分钟数”作为参数，请修改第9行和第12行：

SQL> with test (time_from, time_to) as
  2    (select to_date('09.08.2020 07:00', 'dd.mm.yyyy hh24:mi'),
  3            to_date('09.08.2020 14:00', 'dd.mm.yyyy hh24:mi')
  4            from dual union all
  5     select to_date('09.08.2020 16:00', 'dd.mm.yyyy hh24:mi'),
  6            to_date('09.08.2020 18:00', 'dd.mm.yyyy hh24:mi')
  7            from dual
  8    )
  9  select to_char(time_from + ((column_value - 1) * &&par_minutes) / (24 * 60), 'hh:mi am') time
 10  from test cross join
 11    table(cast(multiset(select level from dual
 12                        connect by level <= (time_to - time_from) * 24 * (60 / &&par_minutes)
 13                       ) as sys.odcinumberlist));
Enter value for par_minutes: 20
old   9: select to_char(time_from + ((column_value - 1) * &&par_minutes) / (24 * 60), 'hh:mi am') time
new   9: select to_char(time_from + ((column_value - 1) * 20) / (24 * 60), 'hh:mi am') time
old  12:                       connect by level <= (time_to - time_from) * 24 * (60 / &&par_minutes)
new  12:                       connect by level <= (time_to - time_from) * 24 * (60 / 20)

TIME
--------
07:00 AM
07:20 AM
07:40 AM
08:00 AM
08:20 AM
08:40 AM
09:00 AM
09:20 AM
09:40 AM
10:00 AM
10:20 AM
10:40 AM
11:00 AM
11:20 AM
11:40 AM
12:00 PM
12:20 PM
12:40 PM
01:00 PM
01:20 PM
01:40 PM
04:00 PM
04:20 PM
04:40 PM
05:00 PM
05:20 PM
05:40 PM

27 rows selected.

SQL>

下面是一种递归cte方法：

with cte (time_from, time_to, lev) as (
      select time_from, time_to, 1 as lev
      from t
      union all
      select time_from + interval '1' hour, time_to, lev + 1
      from cte
      where time_from < time_to - interval '1' hour
     )
select time_from
from cte;

还有一把小提琴。