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;

oracle提供了相当全面的日期（包括时间戳）处理函数。一般来说，我遵循的 axios 是，一旦一列被转换为日期，转换为字符串的唯一原因就是创建一个显示列。但也有例外。其中之一是（经常是）当日期需要切片，切块和重新组装。这就是根据您的“匹配分钟运行”要求正确计算结束时间的情况。
你不会从sql解决方案中得到换行符，至少我会这么做。您可以通过迭代结果来使用pl/sql。但是，对于表示层来说，这是一项简单的任务。我在最后的结果中添加了一列，指出了这种转变。记住这一点：

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;

它的功能：
查询工作在两个CTE中完成，并使用它们进行选择：
时间范围：确定给定当前时间（在服务器上）所需的开始和结束时间。
办公时间：计算从开始到结束时间的15分钟间隔的递归cte。
主：选择范围的起始点并确定班次。
例如看小提琴。注意：在其当前形式中，查询将始终返回至少1行。如果在结束时间之后运行，则返回该时间的行指示。
有两个额外的查询，稍作修改，允许实际指定运行时，而不是从系统中获取运行时。对于这些我已经“设置运行时”在他们的07:00和09:02分别。