MySQL：如何计算两个时间戳之间的工作小时数并忽略周末？

Question

我的表格有样本数据，我需要计算两列中的两个时间戳之间的工作小时数。 工作时间为：上午9点到下午5点，不包括周六和周日，我不考虑公共假期。 请问有人能提供如何实现这个功能的指导吗？ 我希望在第三列中得到所需的输出，日期格式为：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

- yokoyoko

---

你尝试了什么？有什么问题？ - Yahia

我对逻辑感到困惑，需要一些入门指导。 - yokoyoko

一些起点请参见我的下面的答案... - Yahia

Answer 1

这个问题要求不考虑公共假期，因此该答案仅计算工作时间，考虑周末，但忽略可能的公共假期。

如果您需要考虑公共假期，则需要单独建立一个表格，列出公共假期的日期，这些日期可能会因年份、州或国家的不同而有所不同。主公式可能保持不变，但您需要从其结果中减去落在给定日期范围内的公共假期小时数。

让我们创建一个包含各种情况的示例数据表：

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，但它的工作方式不同。它实际上计算秒数差异并除以所选择的unit的秒数（舍弃小数部分）。

因此，为了得到所需的结果，我们可以计算某个锚定日期和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

- Vladimir Baranov

---

这似乎适用于潜在的营业时间，但是实际的营业时间会因为每年变化的假期而需要在某个地方进行明确定义，很可能是在一个表格中。 - JRD

@JRD，你是对的。问题明确说明公共假期应该被忽略，而我在答案中没有重复它。我想我最好还是这样做... - Vladimir Baranov

一种非常聪明的解决方案，尽管如此。 - JRD

@SteveHarrington，标准是每天8个工作小时。开始和结束时间并不重要，公式并不关心。该公式从每个日历日中减去16小时。然后，它会为给定日期范围内的每个周末再次减去额外的小时数。如果您的工作日有10个工作小时而不是8个，则需要在公式的第一部分中将16（= 24-8）更改为14（= 24-10），将第二个16（= 8 + 8）更改为20（= 10 + 10）。 - Vladimir Baranov

@SteveHarrington，该公式假设工作日在07:42之前某个时刻开始，并持续8小时。工作日可以在07:42、05:00或06:30开始，结果都是相同的，因为无论何时开始工作日，工作日持续时间应为8小时。 - Vladimir Baranov

Answer 2

创建一个名为BusinessHours的表，只有一个名为Hour的日期时间列。将Hour作为主键。编写一个过程来填充每年的所有营业时间（例如2012-01-02 00:90:00, 2012-01-02 00:10:00等）。这不难，因为规则很简单。听起来像是大量的数据，但在宏观上并不是（一年只有8760小时 - 即使您的日期时间每个占用8字节，这也只有60KB）。请确保安排一个任务来保持其填充状态。

然后：

Select
  y.CreatedDate,
  y.UpdatedDate,
  Count(*) as BusinessHours
From
  YourTable y
    Inner Join
  BusinessHours h
    On h.Hour >= y.CreatedDate And h.Hour < y.UpdatedDate
Group By
  y.CreatedDate,
  y.UpdatedDate

这也为您提供了一个相当简单的方法，如果您考虑公共假期 - 只需从BusinessHours表中删除行即可。

- Laurence

Answer 3

使用datediff计算开始时间和结束时间之间的差异，然后减去（使用负小时的date_add）非工作时间：周末24小时，工作日16小时，但结束和开始日需要额外计算。可以使用dayofweek方法指定一天是否为周末。

- Dexion

Answer 4

尝试

SELECT (FLOOR(DATEDIFF (UpdatedDate, CreatedDate) / 7) * 5 + MOD (DATEDIFF (UpdatedDate, CreatedDate), 7)) * 8 +
       TIMEDIFF (TIME (UpdatedDate), TIME(CreatedDate))
FROM YourTable

以上内容并不完整，因为它只是“估计”周末...但这应该能帮助你入门...另一个选择是使用有效工作时间表（详见Laurence的这个答案）。

有关参考，请查看MySQL文档。

- Yahia