MySQL-时间戳之间的平均差异，不包括周末和非工作时间 [英] MySQL - Average difference between timestamps, excluding weekends and out of business hours

问题描述

我正在寻找一种对时间戳之间的差异进行平均的功能，不包括周末和非工作时间(仅在08:00:00-17:00:00之间).

I'm looking for the ability to average the difference between timestamps, excluding weekends and excluding out of business hours (only between 08:00:00 - 17:00:00).

我正在尝试仅使用查询来使其工作，但是如果不能使用MySQL，则可以使用PHP函数

I'm trying to get this working using just a query but can fallback to a PHP function if it's not possible using MySQL

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

Below is the current function I am using to get the average timestamp differences.

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

SQLFIDDLE LINK

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

我已经在其他问题上查看了 ，但它们似乎并没有在时间戳中显示小时，而仅显示日期.

I've looked at other questions but they don't seem to work with hours in timestamps, only dates.

我现在正在使用以下存储的功能来实现所需的结果.它将忽略工作时间(08:00:00-17:00:00)以外的时间，并忽略周末.实际上，它只会计算两个时间戳之间的营业时间差.

I am now using the below stored function to achieve my desired result. It will ignore time outside of business hours (08:00:00 - 17:00:00) and ignore weekends. It will essentially only calculate the business hour difference between two timestamps.

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非常难看.但是，如果您可以使用存储的函数，那么它的外观也很漂亮.

Its possible, but very very ugly using sql only. However, if you can use stored functions, then its quite pretty to look at as well.

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

From the SO question you linked in your question, we know the following expression calculates the number of weekdays between two dates:

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

如果我们将此表达式乘以9，即每个工作日#个工作小时，则会得到business hours diff.在两个时间戳之间添加小时调整，我们可以得到最终的表达式，然后可以求平均值

If we multiply this expression by 9, i.e. # working hours per working day, we get the business hours diff. Adding the hours adjustment between the two timestamps gives us the final expression which we can then average

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)

因此，丑陋但有效的查询是:

So, the ugly but working query is:

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逻辑的存储函数.

A better alternative would be to create a stored function that handles the 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;

这篇关于MySQL-时间戳之间的平均差异，不包括周末和非工作时间的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！