oracle计算日期差问题，求高人指点，

[很灵活的胖子]

表有一个任务开始时间，一个任务结束时间，之前是直接通过结束时间减去开始时间获得整个任务的消耗时间。
现在为了更加精确，要求计算的结果排除每天休息的时间，现在每天的休息时间为中午12:00-13:30，
但是这里有一些比较麻烦的事，
如开始时间为 2014-09-21 11:00 ，结束时间为 2014-09-22 14:00，这样需要排除21,22号的休息时间
如果开始时间在休息时间内，那么开始时间就需要从13:30开始算，
如果结束时间在休息时间内，那么结束时间就从12:00截止，
要求就是要获得任务结束时间 - 任务开始时间 - 休息时间

求高人指导~谢谢。

[bw555]

假设起止时间分别为P_STARTTIME P_ENDTIME 试一下吧

SELECT P_ENDTIME-P_STARTTIME-SUM(LEAST(E,P_ENDTIME)-GREATEST(S,P_STARTTIME))
FROM(
select TRUNC(P_STARTTIME)+rownum-1+12/24 S,
TRUNC(P_STARTTIME)+rownum-1+13.5/24 E
from dual connect by rownum<=TRUNC(P_ENDTIME)-TRUNC(P_STARTTIME)+1
)WHERE S<=P_ENDTIME AND E>=P_STARTTIME

[bw555]

sum需要nvl处理一下，因为可能存在没有休息时间的状况

SELECT P_ENDTIME-P_STARTTIME-NVL(SUM(LEAST(E,P_ENDTIME)-GREATEST(S,P_STARTTIME)),0)
FROM(
select TRUNC(P_STARTTIME)+rownum-1+12/24 S,
TRUNC(P_STARTTIME)+rownum-1+13.5/24 E
from dual connect by rownum<=TRUNC(P_ENDTIME)-TRUNC(P_STARTTIME)+1
)WHERE S<=P_ENDTIME AND E>=P_STARTTIME

[很灵活的胖子]

引用 2 楼 bw555 的回复:

sum需要nvl处理一下，因为可能存在没有休息时间的状况

SELECT P_ENDTIME-P_STARTTIME-NVL(SUM(LEAST(E,P_ENDTIME)-GREATEST(S,P_STARTTIME)),0)
FROM(
select TRUNC(P_STARTTIME)+rownum-1+12/24 S,
TRUNC(P_STARTTIME)+rownum-1+13.5/24 E
from dual connect by rownum<=TRUNC(P_ENDTIME)-TRUNC(P_STARTTIME)+1
)WHERE S<=P_ENDTIME AND E>=P_STARTTIME

感谢，尝试了几种，米有问题。

[很灵活的胖子]

引用 3 楼 wildwave 的回复:

(trunc(end_time)-trunc(start_time)-1)*1440

哥，上面这段如果结束时间和开始时间日期相同，相减为0了，再减1将产生负值吧，我之前也是类似这种方式 ，不过写了很多多余的代码

[小灰狼W]

分成3部分，例如9月21日到9月25日，中间的22,23,24每天固定时间为22.5*60分钟；加上头尾两天的工作时间 也就是 (trunc(end_time)-trunc(start_time)-1)*1440 + case when to_char(start_time,'HH24MI')<='1200' then (trunc(start_time+1)-start_time)*1440-1.5*60 when to_char(start_time,'HH24MI')<='1330' then 10.5*60 else (trunc(start_time+1)-start_time)*1440 end + case when to_char(end_time)>='1330' then (end_time-trunc(end_time))*1440-1.5*60 when to_char(end_time)>='1200' then 720 else (end_time-trunc(end_time))*1440

[小灰狼W]

引用 5 楼 tiaotiao031 的回复:

[quote=引用 3 楼 wildwave 的回复:] (trunc(end_time)-trunc(start_time)-1)*1440

哥，上面这段如果结束时间和开始时间日期相同，相减为0了，再减1将产生负值吧，我之前也是类似这种方式 ，不过写了很多多余的代码 [/quote] 负的也没问题。试试就知道