mysql - 日期之间的时差，包括营业时间，不包括节假日

Question

如何计算两个日期之间的时差，考虑到:

• 仅限周一至周五
• 上午 9 点到下午 5:30 之间的时间；
• 不包括节假日。

例子:

• d1 = 2012-10-05 17:00:00
• d2 = 2012-14-09 12:00:00

计算步骤:

• 2012-10-05 = 00:30:00
• 2012-10-06 = 00:00:00
• 2012-10-07 = 00:00:00
• 2012-10-08 = 07:30:00
• 2012-10-09 = 04:00:00

ddiff(d2,d1) = 12:00:00

我知道如何只使用周一至周五，as described here .我说的是 MySQL。

Answer 1

我提出了一个相对简单的解决方案来计算完整 中期日期的时差。然而，使用 mysql 计算开始和结束日期的时差有点麻烦。我已将它们包含在我的解决方案中，但有一些假设。

无论如何，这是sql

SET @startdate:='2012-12-24 17:00:00';
SET @enddate:='2013-01-02 12:00:00';

SELECT
TIME_TO_SEC(TIMEDIFF(CONCAT(DATE(@startdate),' 17:30:00'), @startdate))/3600 as startday_time,
TIME_TO_SEC(TIMEDIFF(@enddate, CONCAT(DATE(@enddate),' 9:00:00')))/3600 as endday_time,
SUM(daily_hours) as otherdays_time from
(
    SELECT 7.5 as daily_hours, id, DATE_ADD(DATE(@startdate),INTERVAL id-1 DAY) as idate from numbers
) dates
LEFT JOIN holidays on DATE(dates.idate) = DATE(holidays.date)
WHERE
idate BETWEEN @startdate AND @enddate
AND holidays.date IS NULL
AND DAYOFWEEK(idate) <> 7 AND DAYOFWEEK(idate) <> 1;

sqlfiddle 在这里: http://sqlfiddle.com/#!2/ff3f3/1/2

要获得有效的中间日期，我们需要两个表 - 一个列出所有假期日期的 holidays 表和一个包含一系列整数的 numbers 表对于加入 against 以获得一系列连续的日期(没有间隔)非常有用。

注意:在 sqlfiddle 中，我只填充了 numbers 表最多 12 个以涵盖我的示例中使用的日期 - 它可能需要填充为更大的数字，具体取决于您将使用的日期范围。

对于开始时间和结束时间，我做了以下假设:

• 开始日期和结束日期都是有效日期，应计入总时间
• 开始日期的时间介于午餐和 17.30 之间
• 结束日期的时间介于午餐和 17.30 之间

如果这些假设是错误的，您将进入严重的条件区域(有很多 ifs)并且最好在 php(或其他)中执行此操作。

注意:出于说明目的，我没有添加时间(以小时为单位)。