mysql - 如何使用MySQL查询获取两个日期之间的确切工作日和小时数？

我必须找到两个日期之间的工作日数，在这些日期之间我需要排除一些日子（例如：每个星期四，星期三，但是这些天是动态的，可以由用户更改）

我在下面附上了示例代码

SELECT CONCAT (
FLOOR(TIMESTAMPDIFF(SECOND, '2016-12-09 17:13:00', '2017-01-01 17:34:00')/(24*60*60)) -
((WEEK('2015-01-01 17:07:00') - WEEK('2014-12-30 21:59:00')) * 2) -
(case when weekday('2017-01-01 17:34:00') = 6 then 1 else 0 end) -
(case when weekday('2016-12-09 21:59:00') = 5 then 1 else 0 end), 'Days ',

TIME_FORMAT(
SEC_TO_TIME((TIMESTAMPDIFF(SECOND, DATE_FORMAT('2016-12-09 21:59:00', '%Y-%m-%d %H:%i'), DATE_FORMAT('2017-01-01 17:34:00', '%Y-%m-%d %H:%i')) -(FLOOR(TIMESTAMPDIFF(SECOND,DATE_FORMAT('2016-12-09 21:59:00', '%Y-%m-%d %H:%i'), DATE_FORMAT('2017-01-01 17:34:00', '%Y-%m-%d %H:%i'))/(24*60*60))*24*60*60))), '%H:%i'), 'Hrs'
)

但是我在周差（（WEEK（'2015-01-01 17:07:00'）-WEEK（'2014-12-30 21:59:00'））* 2）中遇到问题，我也尝试过ROUND（DATEDIFF（end_date，start_date）/ 7，0）代替周差。

最佳答案

如果5周六，6周日排除了2个非工作日，请检查以下查询。

SELECT created_at, updated_at, IF(
(YEAR(created_at)=YEAR(updated_at)),
CONCAT (FLOOR(TIMESTAMPDIFF(SECOND, created_at, updated_at)/(24*60*60)) -
    ((WEEK(updated_at) - WEEK(created_at)) * 2) -
    (case when weekday(updated_at) = 6 then 1 else 0 end) -
    (case when weekday(created_at) = 5 then 1 else 0 end), 'Days ',

    TIME_FORMAT(
        SEC_TO_TIME((TIMESTAMPDIFF(SECOND, DATE_FORMAT(created_at, '%Y-%m-%d %H:%i'), DATE_FORMAT(updated_at, '%Y-%m-%d %H:%i')) -(FLOOR(TIMESTAMPDIFF(SECOND,DATE_FORMAT(created_at, '%Y-%m-%d %H:%i'), DATE_FORMAT(updated_at, '%Y-%m-%d %H:%i'))/(24*60*60))*24*60*60))), '%H:%i'), 'Hrs'
),

CONCAT (
    FLOOR(TIMESTAMPDIFF(SECOND, created_at, updated_at)/(24*60*60)) -
    (((((YEAR(updated_at)-YEAR(created_at))*52)+WEEK(updated_at)) - WEEK(created_at)) * 2) -
    (case when weekday(updated_at) = 6 then 1 else 0 end) -
    (case when weekday(created_at) = 5 then 1 else 0 end), 'Days ',
    TIME_FORMAT(
        SEC_TO_TIME((TIMESTAMPDIFF(SECOND, DATE_FORMAT(created_at, '%Y-%m-%d %H:%i'), DATE_FORMAT(updated_at, '%Y-%m-%d %H:%i')) -(FLOOR(TIMESTAMPDIFF(SECOND,DATE_FORMAT(created_at, '%Y-%m-%d %H:%i'), DATE_FORMAT(updated_at, '%Y-%m-%d %H:%i'))/(24*60*60))*24*60*60))), '%H:%i'), 'Hrs'
)
)
FROM Test_table

关于mysql - 如何使用MySQL查询获取两个日期之间的确切工作日和小时数？，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/45841338/