php mysql 时间差_PHP-MySQL-时间戳之间的平均差,不包括周末和...

它可能,但是仅使用sql非常难看.但是,如果可以使用存储的函数,那么它的外观也很漂亮.

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

5 * (DATEDIFF(@E, @S) DIV 7) +

MID('0123455501234445012333450122234501101234000123450',

7 * WEEKDAY(@S) + WEEKDAY(@E) + 1, 1)

如果我们将此表达式乘以9,即每个工作日#个工作时间,则会得到营业时间差异.将两个时间戳之间的小时调整相加得出最终表达式,然后我们可以求平均

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

更好的选择是创建一个存储的函数来处理营业时间差异逻辑.

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;