多日期时间范围拆分SQL脚本异常：结果不符合预期需求

我编写了如下SQL脚本，用于将单日时间条目保持原样，跨多日的时间条目按日期拆分展开，但当前执行结果不符合预期，正确的拆分结果应覆盖所有涉及的日期，且每日的时间段对应正确。

原SQL脚本：

with  first_step as
(
SELECT  
1 as MY_TYPE,
    2373 as my_id
    ,to_date('15.02.23 17:00'  , 'dd.mm.yyyy HH24:MI') AS  time_from
    ,to_date('17.02.23 12:00' , 'dd.mm.yyyy HH24:MI')AS  time_till
    from dual
    union all
SELECT  
1 as MY_TYPE,
    2373 as my_id
    ,to_date('16.02.23 14:00'  , 'dd.mm.yyyy HH24:MI') AS  time_from
    ,to_date('16.02.23 15:00' , 'dd.mm.yyyy HH24:MI')AS  time_till
    from dual
    union all
SELECT  
0 as MY_TYPE,
    2373 as my_id
    ,to_date('14.02.23 22:00'  , 'dd.mm.yyyy HH24:MI') AS  time_from
    ,to_date('16.02.23 18:00' , 'dd.mm.yyyy HH24:MI')AS  time_till
    from dual
),
second_step as 
(
select 
MY_TYPE,
my_id,
to_date(to_char(time_from +(column_value-1), 'dd.mm.yyyy'),'dd.mm.yyyy') AS  my_date,
case when trunc(time_from)  < to_date(to_char(time_from +(column_value-1), 'dd.mm.yyyy'),'dd.mm.yyyy') then '00:00' else to_char(time_from,'HH24:MI') end time_from,
case when trunc(time_till) > to_date(to_char(time_from +(column_value-1), 'dd.mm.yyyy'),'dd.mm.yyyy') then '23:59' else replace(to_char(time_till,'HH24:MI'),'00:00','23:59') end time_till
from first_step 
   CROSS JOIN TABLE ( CAST(MULTISET(
                SELECT
                   level
                from
                    dual
                CONNECT BY time_from  + level-1    <=    time_till
            ) AS sys.odcinumberlist) ) n
)
select * from second_step 
order by 
my_date,time_from, time_till

脚本存在的核心问题 #

• 日期生成逻辑错误
  原脚本中CONNECT BY time_from + level-1 <= time_till按完整datetime累加判断，会导致跨日但结束时间早于起始时间当天同一小时的条目无法生成最后一天的行。例如第一个条目time_from=15.02.23 17:00、time_till=17.02.23 12:00，当level=3时，time_from+2等于17.02.23 17:00，晚于time_till的17.02.23 12:00，因此不会生成17号的行，导致拆分结果缺失最后一天的数据。

• 日期计算冗余且易出错
  to_date(to_char(time_from +(column_value-1), 'dd.mm.yyyy'),'dd.mm.yyyy')写法冗余，等价于更简洁高效的trunc(time_from) + (column_value - 1)，多次转换容易引发格式或隐式转换问题。

• 时间段判断逻辑冗余
  原脚本重复计算to_date(to_char(time_from +(column_value-1), 'dd.mm.yyyy'),'dd.mm.yyyy')，增加了不必要的计算开销。

修正后的SQL脚本 #

with  first_step as
(
SELECT  
1 as MY_TYPE,
    2373 as my_id
    ,to_date('15.02.23 17:00'  , 'dd.mm.yyyy HH24:MI') AS  time_from
    ,to_date('17.02.23 12:00' , 'dd.mm.yyyy HH24:MI')AS  time_till
    from dual
    union all
SELECT  
1 as MY_TYPE,
    2373 as my_id
    ,to_date('16.02.23 14:00'  , 'dd.mm.yyyy HH24:MI') AS  time_from
    ,to_date('16.02.23 15:00' , 'dd.mm.yyyy HH24:MI')AS  time_till
    from dual
    union all
SELECT  
0 as MY_TYPE,
    2373 as my_id
    ,to_date('14.02.23 22:00'  , 'dd.mm.yyyy HH24:MI') AS  time_from
    ,to_date('16.02.23 18:00' , 'dd.mm.yyyy HH24:MI')AS  time_till
    from dual
),
second_step as 
(
select 
    MY_TYPE,
    my_id,
    trunc(time_from) + (column_value - 1) AS my_date,
    case 
        when trunc(time_from) < trunc(time_from) + (column_value - 1) then '00:00' 
        else to_char(time_from,'HH24:MI') 
    end time_from,
    case 
        when trunc(time_till) > trunc(time_from) + (column_value - 1) then '23:59' 
        else replace(to_char(time_till,'HH24:MI'),'00:00','23:59') 
    end time_till
from first_step 
   CROSS JOIN TABLE ( CAST(MULTISET(
                SELECT level
                from dual
                -- 按日期截断后的天数差生成需要的行数，确保覆盖所有涉及的日期
                CONNECT BY level <= trunc(time_till) - trunc(time_from) + 1
            ) AS sys.odcinumberlist) ) n
)
select * from second_step 
order by my_date, time_from, time_till

修正说明 #

1. 调整CONNECT BY条件为level <= trunc(time_till) - trunc(time_from) + 1，按日期截断后的天数差生成条目，确保所有涉及的日期都能生成对应的行。
2. 简化my_date的计算方式为trunc(time_from) + (column_value - 1)，避免冗余的日期转换。
3. 简化时间段判断中的日期对比逻辑，直接使用截断后的日期进行判断，提升脚本可读性和执行效率。

内容的提问来源于stack exchange，提问作者kirilb