mysql : how to calculate business hrs between two timestamps and neglect weekends

Question

我的表有示例数据，我需要计算两列中两个时间戳之间的营业时间。营业时间:上午 9:00 至下午 5:00，忽略周六和周日，我不考虑公共(public)假期。有人可以提供一些关于如何实现这一目标的指导吗？我想要第 3 列中所述的所需输出，日期格式为:yyyy-mm-dd

    Created date             Updated date         Business hrs
    2012-03-05 9:00am   2012-03-05 3:00pm             6
    2012-03-05 10:00am  2012-03-06 10:00am            9
    2012-03-09 4:00pm   2012-03-19 10:00am            2

Answer 1

这个问题说公共(public)假期不应该被考虑在内，所以这个答案就是这样做的 - 计算营业时间时考虑周末，但忽略可能的公共(public)假期。

如果您需要将公共(public)假期考虑在内，您需要有一个单独的表格来列出公共(public)假期的日期，这些日期可能因年份、州与州或国家/地区而异。主要公式可能保持不变，但您需要从给定日期范围内的公共(public)假期的结果小时数中减去。

让我们用一些涵盖各种情况的示例数据创建一个表:

CREATE TABLE T (CreatedDate datetime, UpdatedDate datetime);

INSERT INTO T VALUES
('2012-03-05 09:00:00', '2012-03-05 15:00:00'), -- simple part of the same day
('2012-03-05 10:00:00', '2012-03-06 10:00:00'), -- full day across the midnight
('2012-03-05 11:00:00', '2012-03-06 10:00:00'), -- less than a day across the midnight
('2012-03-05 10:00:00', '2012-03-06 15:00:00'), -- more than a day across the midnight
('2012-03-09 16:00:00', '2012-03-12 10:00:00'), -- over the weekend, less than 7 days
('2012-03-06 16:00:00', '2012-03-15 10:00:00'), -- over the weekend, more than 7 days
('2012-03-09 16:00:00', '2012-03-19 10:00:00'); -- over two weekends

在 MS SQL Server 中，我使用以下公式:

SELECT
    CreatedDate,
    UpdatedDate,
    DATEDIFF(minute, CreatedDate, UpdatedDate)/60.0 -
    DATEDIFF(day,    CreatedDate, UpdatedDate)*16 -
    DATEDIFF(week,   CreatedDate, UpdatedDate)*16 AS BusinessHours
FROM T

产生以下结果:

+-------------------------+-------------------------+---------------+
|       CreatedDate       |       UpdatedDate       | BusinessHours |
+-------------------------+-------------------------+---------------+
| 2012-03-05 09:00:00     | 2012-03-05 15:00:00     | 6             |
| 2012-03-05 10:00:00     | 2012-03-06 10:00:00     | 8             |
| 2012-03-05 11:00:00     | 2012-03-06 10:00:00     | 7             |
| 2012-03-05 10:00:00     | 2012-03-06 15:00:00     | 13            |
| 2012-03-09 16:00:00     | 2012-03-12 10:00:00     | 2             |
| 2012-03-06 16:00:00     | 2012-03-15 10:00:00     | 50            |
| 2012-03-09 16:00:00     | 2012-03-19 10:00:00     | 42            |
+-------------------------+-------------------------+---------------+

有效，因为在 SQL Server 中 DATEDIFF返回指定 datepart 边界在指定 startdate 和 enddate 之间交叉的计数。

每天有 8 个工作时间。我计算两个日期之间的总小时数，然后减去午夜数乘以每天 16 个非工作时间，然后减去周末数乘以 16(周六 + 周日为 8+8 个工作时间)。

它还假设给定的开始和结束日期/时间是在工作时间内。

在 MySQL 中，最接近的等价物是 TIMESTAMPDIFF , 但它的工作方式不同。它有效地计算秒数的差异并除以所选单位中的秒数(丢弃小数部分)。

因此，为了获得我们需要的结果，我们可以计算一些 anchor 日期时间与 CreatedDate 和 UpdatedDate 之间的 TIMESTAMPDIFF 而不是计算差异直接在 CreatedDate 和 UpdatedDate 之间。

我选择了 2000-01-03 00:00:00，这是星期一。您可以选择任何其他星期一(或星期日，如果您的一周从星期日开始)午夜作为锚定日期。

MySQL 查询变为(参见 SQL Fiddle):

SELECT
    CreatedDate,
    UpdatedDate,

    TIMESTAMPDIFF(MINUTE, CreatedDate, UpdatedDate)/60.0 -

    16*(
        TIMESTAMPDIFF(DAY,    '2000-01-03',UpdatedDate)-
        TIMESTAMPDIFF(DAY,    '2000-01-03',CreatedDate)
    ) -

    16*(
        TIMESTAMPDIFF(WEEK,   '2000-01-03',UpdatedDate)-
        TIMESTAMPDIFF(WEEK,   '2000-01-03',CreatedDate)
    ) AS BusinessHours

FROM T