sql - 两个日期之间的返回时间，周末除外

Question

我需要返回两个日期之间的时间，Oracle除了周末的时间，我可以返回分钟。但是当我设置周末日期时，我会收到一个null结果，而不是工作周的剩余时间。

首先，我们需要创建一个函数：

CREATE OR REPLACE FUNCTION get_bus_minutes_between (start_dt DATE, end_dt DATE)
RETURN NUMBER
IS
    v_return NUMBER;
BEGIN
    select  sum(greatest(end_dt - start_dt,0)) * 24 * 60 work_minutes
      into  v_return
      from  dual
      where trunc(start_dt) - trunc(start_dt,'iw') < 5; -- exclude weekends
    RETURN v_return;
END;

案例 1 - 返回工作周中的分钟数 - 好的

在工作周开始和结束。

SELECT
"GET_BUS_MINUTES_BETWEEN"(TO_DATE('14-09-2020 06:00:00', 'dd-mm-yyyy hh24:mi:ss'), 
                          TO_DATE('14-09-2020 10:00:00', 'dd-mm-yyyy hh24:mi:ss')) "WORK_MINUTES"
FROM
    "SYS"."DUAL";

案例 2 - 返回工作周的剩余分钟数 - 失败 从周末开始，到工作周结束。

SELECT
"GET_BUS_MINUTES_BETWEEN"(TO_DATE('13-09-2020 06:00:00', 'dd-mm-yyyy hh24:mi:ss'), 
                          TO_DATE('14-09-2020 10:00:00', 'dd-mm-yyyy hh24:mi:ss')) "WORK_MINUTES"
FROM
    "SYS"."DUAL";

13-09-2020是星期天，因此我预计返回时间为与星期一相关的 600 分钟。

在这些可能性中，我们可以从工作日开始，到周末结束。

标签： sqloracledatetimerecursive-query

Answer 1

您不需要使用 SQL 或行生成器，只需使用 PL/SQL 进行简单计算即可。改编自我在这里和这里的回答。

CREATE OR REPLACE FUNCTION get_bus_minutes_between (start_dt DATE, end_dt DATE)
RETURN NUMBER
IS
  p_start_date   DATE;
  p_end_date     DATE;
  p_working_days NUMBER;
BEGIN
  IF start_dt IS NULL OR end_dt IS NULL THEN
    RETURN NUll;
  END IF;

  -- Enforce that the values are earliest start date to latest end date.
  p_start_date := LEAST( start_dt, end_dt );
  p_end_date   := GREATEST( start_dt, end_dt );

  -- Calculate the number of days from the beginning of the ISO week containing
  -- the start date and the beginning of the ISO week containing the end date
  -- and then multiply this by 5/7 to get the number of full business days.
  --
  -- Then add on the extra days from the beginining of the ISO week containing
  -- the end date and the end date and subtract the extra days from the
  -- beginning of the ISO week containing the start date to the start date.
  p_working_days := ( TRUNC( p_end_date, 'IW' ) - TRUNC( p_start_date, 'IW' ) ) * 5 / 7
                    + LEAST( p_end_date - TRUNC( p_end_date, 'IW' ), 5 )
                    - LEAST( p_start_date - TRUNC( p_start_date, 'IW' ), 5 );

  -- If the start date and end date are reversed then return a negative value.
  IF start_dt > end_dt THEN
    RETURN -ROUND( p_working_days * 24 * 60, 3 );
  ELSE
    RETURN +ROUND( p_working_days * 24 * 60, 3 );
  END IF;
END;
/

然后：

SELECT GET_BUS_MINUTES_BETWEEN(
         DATE '2020-09-14' + INTERVAL '6' HOUR, 
         DATE '2020-09-14' + INTERVAL '10' HOUR
       ) AS minutes_between
FROM   DUAL;

输出：

| MINUTES_BETWEEN |
| --------------: |
| 240 |

和：

SELECT GET_BUS_MINUTES_BETWEEN(
         DATE '2020-09-13' + INTERVAL '6' HOUR, 
         DATE '2020-09-14' + INTERVAL '10' HOUR
       ) AS minutes_between
FROM   DUAL;

输出：

| MINUTES_BETWEEN |
| --------------: |
| 600 |

db<>在这里摆弄