问在今天的两次之间生成时隙

EN

WITH 
--cte to determine office hours, this is probably a table irl
office_timing (id, time_from2, time_to2) AS
(
  SELECT 1, TO_DATE('09/08/2020 07:00:00 AM','DD/MM/YYYY HH:MI:SS AM'),     TO_DATE('09/08/2020 02:00:00 PM','DD/MM/YYYY HH:MI:SS AM') FROM dual UNION ALL
  SELECT 2, TO_DATE('09/08/2020 04:00:00 PM','DD/MM/YYYY HH:MI:SS AM'),     TO_DATE('09/08/2020 06:00:00 PM','DD/MM/YYYY HH:MI:SS AM') FROM dual
)
--cte to determine when travel time to office, replace with other values to test. Make this a variable if it is an input parameter
,travel_time (travel_mins) AS
(
  SELECT 31 FROM DUAL
)
--cte to determine slot length, replace with other values to test. Make this a variable if it is an input parameter
,
slot_minutes (mins) AS
(
  SELECT 15 FROM DUAL
)
--cte to determine when query is run, replace with other values to test. Make this a variable if it is an input parameter
,run_date_tab (run_date) AS
(
SELECT 
    TO_DATE('09/08/2020 03:45:00 PM','DD/MM/YYYY HH:MI:SS AM') + travel_mins/1440    
  FROM travel_time
)

--cte to determine start time based on the query run date
--  if run date is in a time slot then take run date
--  if run date is outside time slot then take closest future start date
,
start_time_tab (qry_start_time) AS
(
  SELECT MIN(CASE 
           WHEN  t.time_from2 <= r.run_date AND t.time_to2 > r.run_date
             THEN r.run_date
           WHEN  t.time_from2 > r.run_date 
             THEN t.time_from2
         ELSE 
           NULL
         END)
    FROM run_date_tab r  
         CROSS JOIN office_timing t
        
)
,slots (slot_start_time) AS
(
  SELECT 
    s.qry_start_time +(level - 1) / ((60/m.mins)*24)
    FROM start_time_tab s CROSS JOIN slot_minutes m CONNECT BY
    level < 100
)
SELECT TO_CHAR(s.slot_start_time,'DD/MM/YYYY HH:MI:SS AM')  
  FROM slots s
       JOIN office_timing t ON t.time_from2 < s.slot_start_time AND t.time_to2 > s.slot_start_time;

with time_range (sts, ets)  as 
     ( select case when extract(hour from systimestamp) <= 07
                   then trunc(systimestamp) + interval '07:00' hour to minute 
                   else trunc(systimestamp, 'mi') 
               end sot            
            , case when extract(hour from systimestamp) <= 07
                   then trunc(systimestamp) + interval '18:00' hour to minute  
                   else to_timestamp(to_char(systimestamp,'yyyymmdd') || '18' || to_char(systimestamp,'mi'), 'yyyymmddhh24mi')  
               end eot             
         from dual
     ) 
    , office_hours (start_time, end_time) as
      ( select * from time_range
        union all
        select start_time+interval '15' minute, end_time
          from office_hours
         where start_time < end_time 
      ) 
select to_char(start_time, 'hh.mi am')
     , case when 60 * extract(hour   from cast( start_time as timestamp))
                    + extract(minute from cast( start_time as timestamp)) <= 14*60 
            then 'first shift'
            else 'second shift'
       end shift
  from office_hours;