SQL 日期差异忽略夜晚

Question

使用 Oracle 数据库，我需要知道任意两个日期之间的差异（以秒为单位），不考虑从下午 6 点到上午 8 点的时间间隔。

例如，01.11.2011 1pm 和 03.11.2011 1pm 之间的差异相当于 20 小时（或分别为 72000 秒）。我还需要忽略周末和节假日，但我已经为此提供了 PLSQL 函数。

先感谢您！

问候， 亚历克斯

编辑
与此同时，我自己想出了一个解决方案，老实说，它不是很复杂，但很有效：

CREATE OR REPLACE FUNCTION "GET_WORKTIME" 
(
  startdate_in in date,
  enddate_in   in date
)
RETURN NUMBER
AS
  days_between number(4);
  duration number;
  end_of_first_day date;
  start_of_last_day date;
  startdate date;
  enddate date;
  weekday number(1);
  temp_date date;
  holidays day_array;
  is_holiday boolean;
BEGIN
  duration     := 0;
  startdate    := startdate_in;
  enddate      := enddate_in;

IF (startdate IS NULL OR enddate IS NULL OR startdate >= enddate) THEN
  RETURN 0;
END IF;

days_between := trunc(enddate) - trunc(startdate);

end_of_first_day  := to_date(concat(to_char(startdate, 'dd.mm.yyyy'), ' 18:00:00'), 'dd.mm.yyyy hh24:mi:ss');
start_of_last_day := to_date(concat(to_char(enddate,   'dd.mm.yyyy'), ' 08:00:00'), 'dd.mm.yyyy hh24:mi:ss');

temp_date := startdate;

FOR i IN 0..days_between LOOP
    -- if it is the first day, just calculate the time until the end of that day
    IF i = 0 THEN
        duration := duration + greatest((end_of_first_day - startdate), 0) * 24 * 3600;

    -- if it is the last day, just calculate the time until that point in time
    ELSIF i = days_between THEN
        duration := duration + greatest((enddate - start_of_last_day),  0) * 24 * 3600;            

    -- for every other day, simply add 10 hours (6pm - 8am) if it is neither
    -- saturday or sunday nor a holiday
    ELSE
        weekday    := to_number(to_char(temp_date, 'D'));
        is_holiday := false;

        -- weekend?
        IF (NOT weekday IN (6,7)) THEN

            -- holiday?
            FOR j IN extract(year FROM startdate)..extract(year FROM enddate) LOOP
                holidays := get_holidays_for_year(j);

                FOR k IN 1..holidays.count LOOP
                    IF (holidays(k) = trunc(temp_date)) THEN
                        is_holiday := true;
                    END IF;
                END LOOP;
            END LOOP;

            -- add 10 hours to the duration
            IF (NOT is_holiday) THEN
                duration := duration + 10 * 3600;
            END IF;
        END IF;
    END IF;

    temp_date := temp_date + 1;
END LOOP;
RETURN duration;
END;

该功能还忽略周末和节假日，所以它看起来比实际更全面一些。感谢您的帮助！

问候， 亚历克斯

Answer 1

将其分为 3 部分可能是最简单的：

1. 第一个部分日（如果有）
2. 最后一个部分日（如果有）
3. 中间的整天（如果有）

第一个部分日是从（开始时间，上午 8 点）中较晚的一天开始到（last_date，第一天下午 6 点）中较早的时间，因此我们减去这些值以获得第一天的时间：

greatest ( least ( trunc(first_date)+18/24
                        , last_date
                        ) - greatest( first_date
                                    , trunc(first_date)+8/24
                                    )
                , 0) * 24 as last_day_hours

外部 greatest 函数确保我们不会得到负值，例如 if开始时间晚于下午 6 点。

如果最后一天与第一天不同，则最后一天的部分时间是从上午 8 点到最后一天的（结束时间）：

   case when trunc(last_date) != trunc(first_date) 
        then greatest ( least( last_date
                             , trunc(last_date)+18/24
                             )
                        - (trunc(last_date)+8/24)
                      , 0) * 24
        else 0 end as last_day_hours

中间的整天是（以小时为单位）：

   greatest ( (trunc(last_date) - (trunc(first_date)+1))
            , 0
            ) * 10 as intervening_days_in_hours

10 是上午 8 点到下午 6 点之间的小时数

因此，将这 3 个值加在一起就得到了该时间段的总小时数，将总数乘以 3600 就得到了秒数：

(
   (greatest ( least ( trunc(first_date)+18/24
                    , last_date
                    ) - greatest( first_date
                                , trunc(first_date)+8/24
                                )
            , 0) * 24)
 + case when trunc(last_date) != trunc(first_date) 
        then greatest ( least( last_date
                             , trunc(last_date)+18/24
                             )
                        - (trunc(last_date)+8/24)
                      , 0) * 24
        else 0 end 
 + (greatest ( (trunc(last_date) - (trunc(first_date)+1))
            , 0
            ) * 10)
) * 3600 as total_seconds

不过我不禁觉得它应该比这更容易！当然，这没有考虑周末。

Answer 2

在 Oracle 中，您可以简单地减去两个日期。
您可以通过将结果除以（24*60*60）来计算秒数

select (last_date - first_date) / (24*60*60) as seconds from table

格式化差异的技巧是这样的：

select to_char(trunc(sysdate) + (last_date - first_date), 'hh24:mi:ss') as time_between from table