参数:开始时间,结束时间
返回:时间差秒数要求:
每天12:00-14:00, 21:00-第二天8:30为休息时间段
1.跨这两个时间段,忽略这两个时间段的时间.如开始时间为11:00,结束时间为15:00,按(11:00-12:00) + (14:00-15:00)计算
2.开始时间在12:00-14:00,按14:00开始算;在21:00-第二天8:30,按第二天8:30开始算
3.结束时间在12:00-14:00,按12:00开始算;在21:00-第二天8:30,按21:00开始算
返回:时间差秒数要求:
每天12:00-14:00, 21:00-第二天8:30为休息时间段
1.跨这两个时间段,忽略这两个时间段的时间.如开始时间为11:00,结束时间为15:00,按(11:00-12:00) + (14:00-15:00)计算
2.开始时间在12:00-14:00,按14:00开始算;在21:00-第二天8:30,按第二天8:30开始算
3.结束时间在12:00-14:00,按12:00开始算;在21:00-第二天8:30,按21:00开始算
SELECT
TO_NUMBER(TO_CHAR(SYSDATE,'DD')) - TO_NUMBER(TO_CHAR(mrp_date,'DD')) AS 天,
TO_NUMBER(TO_CHAR(SYSDATE,'HH24'))- TO_NUMBER(TO_CHAR(mrp_date,'HH24')) AS 時,
TO_NUMBER(TO_CHAR(SYSDATE,'MI'))- TO_NUMBER(TO_CHAR(mrp_date,'MI')) AS 分,
TO_NUMBER(TO_CHAR(SYSDATE,'SS')) - TO_NUMBER(TO_CHAR(mrp_date,'SS')) AS 秒
FROM erp.nid_mrp WHERE to_char(mrp_date,'yyyymmdd')='20081011' AND ROWNUM = 1;
RETURN NUMBER
AS
v_begin_time DATE;
v_end_time DATE;
v_worktime NUMBER;
BEGIN
IF TO_CHAR (p_begin, 'hh24mi') BETWEEN '1200' AND '1400'
THEN
v_begin_time := TRUNC (p_begin) + INTERVAL '14' HOUR;
ELSIF TO_CHAR (p_begin, 'hh24mi') BETWEEN '2100' AND '2359'
OR TO_CHAR (p_begin, 'hh24mi') BETWEEN '0000' AND '0830'
THEN
IF TO_CHAR (p_begin, 'hh24mi') BETWEEN '2100' AND '2359'
THEN
v_begin_time :=
TRUNC (p_begin + 1) + INTERVAL '08' HOUR
+ INTERVAL '30' MINUTE;
ELSE
v_begin_time :=
TRUNC (p_begin) + INTERVAL '08' HOUR
+ INTERVAL '30' MINUTE;
END IF;
ELSE
v_begin_time := p_begin;
END IF; DBMS_OUTPUT.put_line (v_begin_time); IF TO_CHAR (p_end, 'hh24mi') BETWEEN '1200' AND '1400'
THEN
v_end_time := TRUNC (p_end) + INTERVAL '12' HOUR;
ELSIF TO_CHAR (p_end, 'hh24mi') BETWEEN '2100' AND '2359'
OR TO_CHAR (p_end, 'hh24mi') BETWEEN '0000' AND '0830'
THEN
IF TO_CHAR (p_end, 'hh24mi') BETWEEN '2100' AND '2359'
THEN
v_end_time := TRUNC (p_end) + INTERVAL '21' HOUR;
ELSE
v_end_time := TRUNC (p_end) - INTERVAL '3' HOUR;
END IF;
ELSE
v_end_time := p_end;
END IF; DBMS_OUTPUT.put_line (v_end_time); IF TO_CHAR (v_end_time, 'hh24miss') >= TO_CHAR (v_begin_time, 'hh24miss')
THEN
v_worktime :=
(v_end_time - v_begin_time) * 24
- 2
- (TRUNC (v_end_time) - TRUNC (v_begin_time)) * 13.5;
ELSE
v_worktime :=
(v_end_time - v_begin_time) * 24
- 11.5
- (TRUNC (v_end_time - 1) - TRUNC (v_begin_time)) * 13.5;
END IF; RETURN v_worktime*60*60;
END;
/