我正在寻找能够平均时间戳之间差异的能力，排除周末和排除非工作时间（仅在 08:00:00 - 17:00:00 之间）。

我试图仅使用查询来实现此功能，但如果无法使用 MySQL，则可以回退到 PHP 函数

下面是我当前用来获取平均时间戳差异的函数。

例如。下面的查询将返回周五上午 8 点到周一下午 5 点之间的差异 81 小时，它需要返回 18 小时，因为它应该排除周末和工作日的非办公时间。

SQLFIDLE链接

SELECT 
    clients.name, 
    avg(TIMESTAMPDIFF(HOUR, jobs.time_created, jobs.time_updated)) AS average_response, 
    avg(TIMESTAMPDIFF(HOUR, jobs.time_created, jobs.time_closed)) AS average_closure, 
    count(jobs.id) AS ticket_count, 
    SUM(time_total) AS time_spent 
FROM 
    jobs
LEFT JOIN 
    clients ON jobs.client = clients.id 
WHERE 
    jobs.status = 'closed' 
GROUP BY 
    jobs.client

我看过在其他问题上但它们似乎不适用于时间戳中的小时，仅适用于日期。

Result

我现在正在使用下面的存储函数来实现我想要的结果。它将忽略工作时间以外的时间（08:00:00 - 17:00:00）并忽略周末。它本质上只会计算两个时间戳之间的营业时间差异。

DROP FUNCTION IF EXISTS BUSINESSHOURSDIFF;
DELIMITER $$
CREATE FUNCTION BUSINESSHOURSDIFF(start_time TIMESTAMP, end_time TIMESTAMP)
RETURNS INT UNSIGNED
BEGIN
IF HOUR(start_time) > 17 THEN SET start_time = CONCAT_WS(' ', DATE(start_time), '17:00:00');
END IF;
IF HOUR(start_time) < 8 THEN SET start_time = CONCAT_WS(' ', DATE(start_time), '08:00:00');
END IF;
IF HOUR(end_time) > 17 THEN SET end_time = CONCAT_WS(' ', DATE(end_time), '17:00:00');
END IF;
IF HOUR(end_time) < 8 THEN SET end_time = CONCAT_WS(' ', DATE(end_time), '08:00:00');
END IF;
RETURN 45 * (DATEDIFF(end_time, start_time) DIV 7) + 
          9 * MID('0123455501234445012333450122234501101234000123450', 
                  7 * WEEKDAY(start_time) + WEEKDAY(end_time) + 1, 1) + 
          TIMESTAMPDIFF(HOUR, DATE(end_time), end_time) - 
          TIMESTAMPDIFF(HOUR, DATE(start_time), start_time);
END $$
DELIMITER ;

这是可能的，但仅使用 sql 非常非常难看。但是，如果您可以使用存储函数，那么看起来也很漂亮。

从您在问题中链接的SO问题中，我们知道以下表达式计算两个日期之间的工作日数：

5 * (DATEDIFF(@E, @S) DIV 7) + 
    MID('0123455501234445012333450122234501101234000123450', 
        7 * WEEKDAY(@S) + WEEKDAY(@E) + 1, 1)

如果我们将此表达式乘以 9，即每个工作日的工作小时数，我们会得到business hours diff。添加两个时间戳之间的小时调整可以得到最终的表达式，然后我们可以对其进行平均

45 * (DATEDIFF(@E, @S) DIV 7) + 
      9 * MID('0123455501234445012333450122234501101234000123450', 
              7 * WEEKDAY(@S) + WEEKDAY(@E) + 1, 1) + 
      TIMESTAMPDIFF(HOUR, DATE(@E), @E) - 
      TIMESTAMPDIFF(HOUR, DATE(@S), @S)

所以，丑陋但有效的查询是：

SELECT 
  clients.name
, AVG(45 * (DATEDIFF(jobs.time_updated, jobs.time_created) DIV 7) + 
          9 * MID('0123455501234445012333450122234501101234000123450', 
                  7 * WEEKDAY(jobs.time_created) + WEEKDAY(jobs.time_updated) + 1, 1) + 
          TIMESTAMPDIFF(HOUR, DATE(jobs.time_updated), jobs.time_updated) - 
          TIMESTAMPDIFF(HOUR, DATE(jobs.time_created), jobs.time_created)) AS average_response
, AVG(45 * (DATEDIFF(jobs.time_closed, jobs.time_created) DIV 7) + 
          9 * MID('0123455501234445012333450122234501101234000123450', 
                  7 * WEEKDAY(jobs.time_created) + WEEKDAY(jobs.time_closed) + 1, 1) + 
          TIMESTAMPDIFF(HOUR, DATE(jobs.time_closed), jobs.time_closed) - 
          TIMESTAMPDIFF(HOUR, DATE(jobs.time_created), jobs.time_created)) AS average_closure
, COUNT(jobs.id) AS ticket_count 
, SUM(time_total) AS time_spent 
FROM jobs
LEFT JOIN clients ON jobs.client = clients.id 
WHERE jobs.status = 'closed' 
GROUP BY jobs.client

更好的选择是创建一个存储函数来处理business hours diff logic.

DROP FUNCTION IF EXISTS BUSINESSHOURSDIFF;
DELIMITER $$    
CREATE FUNCTION BUSINESSHOURSDIFF(start_time TIMESTAMP, end_time TIMESTAMP) 
RETURNS INT UNSIGNED
BEGIN
RETURN 45 * (DATEDIFF(end_time, start_time) DIV 7) + 
          9 * MID('0123455501234445012333450122234501101234000123450', 
                  7 * WEEKDAY(start_time) + WEEKDAY(end_time) + 1, 1) + 
          TIMESTAMPDIFF(HOUR, DATE(end_time), end_time) - 
          TIMESTAMPDIFF(HOUR, DATE(start_time), start_time);
END $$
DELIMITER ;

然后根据需要调用它。

SELECT 
    clients.name
  , avg(BUSINESSHOURSDIFF(jobs.time_created, jobs.time_updated)) AS average_response
  , avg(BUSINESSHOURSDIFF(jobs.time_created, jobs.time_closed)) AS average_closure
  , count(jobs.id) AS ticket_count
  , SUM(time_total) AS time_spent 
FROM jobs
LEFT JOIN clients ON jobs.client = clients.id 
WHERE jobs.status = 'closed' 
GROUP BY jobs.client;