oracle 2个日期之间的时间范围

k3fezbri 于 2023-02-15 发布在 Oracle

关注(0)|答案(1)|浏览(146)

我有以下脚本

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

这就是我得到

但我需要那个

因此，我们有在同一天的条目，但也有持续多天的条目。单日条目应该保持原样，但多天条目应该延长。目前我的多天条目没有正确表示。我的脚本有什么问题？

oracle

来源：https://stackoverflow.com/questions/75444525/time-range-between-2-dates

1条答案

按热度按时间

rryofs0p1#

您可以使用递归查询：

with first_step (my_type, my_id, time_from, time_till) as (
  SELECT 1,
         2373,
         to_date('15.02.2023 17:00', 'dd.mm.yyyy HH24:MI'),
         to_date('17.02.2023 12:00', 'dd.mm.yyyy HH24:MI')
  from   dual
union all
  SELECT 1,
         2373,
         to_date('16.02.2023 14:00', 'dd.mm.yyyy HH24:MI'),
         to_date('16.02.2023 15:00', 'dd.mm.yyyy HH24:MI')
  from   dual
union all
  SELECT 0,
         2373,
         to_date('14.02.2023 22:00', 'dd.mm.yyyy HH24:MI'),
         to_date('16.02.2023 18:00', 'dd.mm.yyyy HH24:MI')
  from   dual
),
days (my_type, my_id, time_from, day_end, time_till) AS (
  SELECT my_type,
         my_id,
         time_from,
         TRUNC(time_from) + INTERVAL '23:59:59' HOUR TO SECOND,
         time_till
  FROM   first_step
UNION ALL
  SELECT my_type,
         my_id,
         day_end + INTERVAL '1' SECOND,
         day_end + INTERVAL '1' DAY,
         time_till
  FROM   days
  WHERE  day_end < time_till
)
SELECT my_type,
       my_id,
       time_from,
       LEAST(day_end, time_till) AS time_till
FROM   days
ORDER BY my_id, time_from, time_till;

或分层查询：

with first_step (my_type, my_id, time_from, time_till) as (
  SELECT 1,
         2373,
         to_date('15.02.2023 17:00', 'dd.mm.yyyy HH24:MI'),
         to_date('17.02.2023 12:00', 'dd.mm.yyyy HH24:MI')
  from   dual
union all
  SELECT 1,
         2373,
         to_date('16.02.2023 14:00', 'dd.mm.yyyy HH24:MI'),
         to_date('16.02.2023 15:00', 'dd.mm.yyyy HH24:MI')
  from   dual
union all
  SELECT 0,
         2373,
         to_date('14.02.2023 22:00', 'dd.mm.yyyy HH24:MI'),
         to_date('16.02.2023 18:00', 'dd.mm.yyyy HH24:MI')
  from   dual
)
SELECT my_type,
       my_id,
       GREATEST(time_from, day_start) AS time_from,
       LEAST(time_till, day_end) AS time_till
FROM   first_step f
       CROSS JOIN LATERAL (
         SELECT TRUNC(f.time_from) + LEVEL - INTERVAL '1' DAY AS day_start,
                TRUNC(f.time_from) + LEVEL - INTERVAL '1' SECOND AS day_end
         FROM   dual
         CONNECT BY TRUNC(f.time_from) + LEVEL - 1 < f.time_till
       )
ORDER BY my_id, time_from, time_till

两者都输出：
| 我的类型|我的ID|开始时间|时间|
| - ------|- ------|- ------|- ------|
| 无|小行星2373|2023年2月14日22时00分|2023年2月14日23时59分59秒|
| 无|小行星2373|2023年2月15日00时00分|2023年2月15日23时59分59秒|
| 1个|小行星2373|2023年2月15日17时00分|2023年2月15日23时59分59秒|
| 无|小行星2373|2023年2月16日00时00分|2023年2月16日18时00分|
| 1个|小行星2373|2023年2月16日00时00分|2023年2月16日23时59分59秒|
| 1个|小行星2373|2023年2月16日14时00分|2023年2月16日15时00分|
| 1个|小行星2373|2023年2月17日00时00分|2023年2月17日12时00分|

注意：to_date('16.02.23 14:00', 'dd.mm.yyyy HH24:MI')将为您提供年份0023而不是2023。如果您需要2023，请使用4位数年份或格式模型RR或YY。*

fiddle

赞(0）回复(0）举报 2023-02-15

我来回答

oracle 2个日期之间的时间范围

1条答案

相关问题

热门标签

最新问答