我正在编辑我的问题小
我有办公室时间表如下。
TIME_FROM TIME_TO TIME_FROM1 TIME_TO1 TIME_FROM2 TIME_TO2
07:00 AM 14:00 PM 700 1400 06/08/2020 07:00:00 AM 06/08/2020 02:00:00 PM
16:00 PM 18:00 PM 1600 1800 06/08/2020 04:00:00 PM 06/08/2020 06:00:00 PM
办公室开始时间为上午7点，结束时间为下午6点，中间有休息时间。此时间可能因所选办公室而异。
输入参数为
1.到达办公室的行程时间(分钟(
2.以分钟为单位的时隙持续时间
在考虑了以分钟为单位的旅行时间后，我想在这些时间范围之间生成15分钟(可变(间隔的时隙，如
上午7:00
上午7点15分
上午7:30
上午7点45分
上午8点.
下午1:30
下午1.45
下午2:00
二班从这里开始
下午4:00
下午4点15分
下午4:30
下午5:30
下午5时45分
场景1:
所需行程时间：31分钟
预订尝试时间上午6.15
办公时间上午7点
要求结果
7.00
7.15
下午1.45(不包括轮班结束时间下午2.00(
下午4:00
下午4点15分
下午5时45分
场景2:
所需行程时间：31分钟
预订尝试时间：上午6.45
办公时间上午7点
要求结果
7.16
7.31
下午1.46(不包括轮班结束时间下午2.00(
下午4:00
下午4点15分
下午5时45分
场景3：
所需行程时间：31分钟
预订尝试时间上午9点45分
办公时间上午7点
要求结果
10.16
10.31
下午1.46(不包括轮班结束时间下午2.00(
下午4:00
下午4点15分
下午5时45分
场景4:
所需行程时间：31分钟
预订尝试时间下午3点
办公室二班开放时间下午4点
要求结果
下午4:00
下午4点15分
下午5点45分(不包括轮班结束时间下午18点(
场景5:
所需行程时间：31分钟
预订尝试时间下午3点45分
办公室二班开放时间下午4点
要求结果
下午4点16分
下午4点31分
下午5点46分(不包括轮班结束时间下午18点(
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提供了相当全面的日期(包括时间戳(处理功能。一般来说，我遵循这样一条公理，即一旦一列被转换为日期，转换为字符串的唯一原因就是创建一个显示列。但也有例外。其中之一是(通常是(需要将日期切片、切成丁并重新组装的时候。这里的情况就是通过您的"匹配分钟跑"要求来正确计算结束时间
你不会从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;
它的作用：
查询工作在2个CTE中完成，并使用它们进行选择：
- time_range：在给定当前时间(在服务器上(
- office_hours:计算15分钟间隔的递归CTE开始到结束时间
- Main：选择范围的起始点并确定换档
例如，请参阅fiddle。注意：在当前形式中，查询将始终返回至少1行。如果在结束时间之后运行，它将返回一行指示该时间
有两个额外的查询，稍作修改，允许实际指定运行时间，而不是从系统中获取。对于这些，我有"；设置运行时间"；分别到07:00和09:02。