MySQL MySQL函数查找两个日期之间的工作日数

MySQL function to find the number of working days between two dates

提问by Yada

Excel has NETWORKDAYS() function that find the number of business days between two dates.

Excel 具有 NETWORKDAYS() 函数，用于查找两个日期之间的工作日数。

Anybody have a similar function for MySQL? Since holidays adds complexity, the solution doesn't have to deal with holidays.

有人对 MySQL 有类似的功能吗？由于假期增加了复杂性，因此解决方案不必处理假期。

回答by Rodger Bagnall

This expression -

这个表情——

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

calculates the number of business days between the start date @S and the end date @E.

计算开始日期@S 和结束日期@E 之间的工作日数。

Assumes end date (@E) is not before start date (@S). Compatible with DATEDIFF in that the same start date and end date gives zero business days. Ignores holidays.

假设结束日期 (@E) 不早于开始日期 (@S)。与 DATEDIFF 兼容，因为相同的开始日期和结束日期提供零个工作日。忽略节假日。

The string of digits is constructed as follows. Create a table of start days and end days, the rows must start with monday (WEEKDAY 0) and the columns must start with Monday as well. Fill in the diagonal from top left to bottom right with all 0 (i.e. there are 0 working days between Monday and Monday, Tuesday and Tuesday, etc.). For each day start at the diagonal (must always be 0) and fill in the columns to the right, one day at a time. If you land on a weekend day (non business day) column, the number of business days doesn't change, it is carried from the left. Otherwise, the number of business days increases by one. When you reach the end of the row loop back to the start of the same row and continue until you reach the diagonal again. Then go on to the next row.

数字串的构造如下。创建一个开始日期和结束日期的表，行必须从星期一 (WEEKDAY 0) 开始，列也必须从星期一开始。将左上角到右下角的对角线全0填入（即周一到周一、周二到周二等有0个工作日）。每天从对角线开始（必须始终为 0）并填写右侧的列，一次一天。如果您登陆周末（非工作日）列，则工作日数不会改变，它从左边开始。否则，工作日数增加 1。当您到达行的末尾时，循环回到同一行的开头并继续，直到再次到达对角线。然后继续下一行。

E.g. Assuming Saturday and Sunday are not business days -

例如，假设周六和周日不是工作日 -

 | M T W T F S S
-|--------------
M| 0 1 2 3 4 4 4
T| 4 0 1 2 3 3 3
W| 3 4 0 1 2 2 2
T| 2 3 4 0 1 1 1
F| 1 2 3 4 0 0 0
S| 1 2 3 4 5 0 0
S| 1 2 3 4 5 5 0

Then concatenate the 49 values in the table into the string.

然后将表中的 49 个值连接成字符串。

Please let me know if you find any bugs.

如果您发现任何错误，请告诉我。

-Edit improved table:

- 编辑改进表：

 | M T W T F S S
-|--------------
M| 0 1 2 3 4 4 4
T| 4 0 1 2 3 3 3
W| 3 4 0 1 2 2 2
T| 2 3 4 0 1 1 1
F| 1 2 3 4 0 0 0
S| 0 1 2 3 4 0 0
S| 0 1 2 3 4 4 0

improved string: '0123444401233334012222340111123400001234000123440'

改进的字符串：'0123444401233334012222340111123400001234000123440'

improved expression:

改进的表达：

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

回答by Bryan Geraghty

This solution uses basically the same approach as Rodger's except the method for generating the matrix is much more complex. Note: This output of this solution is not compatible with NETWORKDAYS.

该解决方案使用的方法与 Rodger 的方法基本相同，只是生成矩阵的方法要复杂得多。注意：此解决方案的此输出与 NETWORKDAYS 不兼容。

As in Rodger's solution, this calculates the number of business days between the start date (@S) and the end date (@E) without having to define a stored procedure. It assumes that the end date is not before the start date. Using the same start and end date will produce 0. Holidays are not taken into account.

与 Rodger 的解决方案一样，这会计算开始日期 (@S) 和结束日期 (@E) 之间的工作日数，而无需定义存储过程。它假定结束日期不早于开始日期。使用相同的开始和结束日期将产生 0。不考虑假期。

The major difference between this and Rodger's solution is that the matrix and resulting string of digits is constructed by a complex algorithm which I have not included. The output of this algorithm is validated by a unit test (see the test inputs and outputs below). In the matrix, the intersection of any given x and y value pair (WEEKDAY(@S) and WEEKDAY(@E) yields the difference in work days between the two values. The assignment order is actually unimportant as the two are added together to plot the position.

这与 Rodger 的解决方案之间的主要区别在于，矩阵和结果数字串是由我没有包括的复杂算法构建的。该算法的输出由单元测试验证（请参阅下面的测试输入和输出）。在矩阵中，任何给定的 x 和 y 值对（WEEKDAY(@S) 和 WEEKDAY(@E) 的交集产生两个值之间的工作日差异。分配顺序实际上并不重要，因为这两个值相加得到绘制位置。

Business days are Monday-Friday

工作日为周一至周五

 | M T W T F S S
-|--------------
M| 0 1 2 3 4 5 5
T| 5 0 1 2 3 4 4
W| 4 5 0 1 2 3 3
T| 3 4 5 0 1 2 2
F| 2 3 4 5 0 1 1
S| 0 1 2 3 4 0 0
S| 0 1 2 3 4 5 0

The 49 values in the table are concatenated into the following string:

表中的 49 个值连接成以下字符串：

0123455501234445012333450122234501101234000123450

In the end, the correct expression is:

最后，正确的表达是：

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

I have verified the following inputs and outputs using this solution:

我已使用此解决方案验证了以下输入和输出：

Sunday, 2012-08-26 -> Monday, 2012-08-27 = 0
Sunday, 2012-08-26 -> Sunday, 2012-09-02 = 5
Monday, 2012-08-27 -> Tuesday, 2012-08-28 = 1
Monday, 2012-08-27 -> Monday, 2012-09-10 = 10
Monday, 2012-08-27 -> Monday, 2012-09-17 = 15
Monday, 2012-08-27 -> Tuesday, 2012-09-18 = 16
Monday, 2012-08-27 -> Monday, 2012-09-24 = 20
Monday, 2012-08-27 -> Monday, 2012-10-01 = 25
Tuesday, 2012-08-28 -> Wednesday, 2012-08-29 = 1
Wednesday, 2012-08-29 -> Thursday, 2012-08-30 = 1
Thursday, 2012-08-30 -> Friday, 2012-08-31 = 1
Friday, 2012-08-31 -> Saturday, 2012-09-01 = 1
Saturday, 2012-09-01 -> Sunday, 2012-09-02 = 0
Sunday, 2012-09-02 -> Monday, 2012-09-03 = 0
Monday, 2012-09-03 -> Tuesday, 2012-09-04 = 1
Tuesday, 2012-09-04 -> Wednesday, 2012-09-05 = 1
Wednesday, 2012-09-05 -> Thursday, 2012-09-06 = 1
Thursday, 2012-09-06 -> Friday, 2012-09-07 = 1
Friday, 2012-09-07 -> Saturday, 2012-09-08 = 1
Saturday, 2012-09-08 -> Sunday, 2012-09-09 = 0
Monday, 2012-09-24 -> Sunday, 2012-10-07 = 10
Saturday, 2012-08-25 -> Saturday, 2012-08-25 = 0
Saturday, 2012-08-25 -> Sunday, 2012-08-26 = 0
Saturday, 2012-08-25 -> Monday, 2012-08-27 = 0
Saturday, 2012-08-25 -> Tuesday, 2012-08-28 = 1
Saturday, 2012-08-25 -> Wednesday, 2012-08-29 = 2
Saturday, 2012-08-25 -> Thursday, 2012-08-30 = 3
Saturday, 2012-08-25 -> Friday, 2012-08-31 = 4
Saturday, 2012-08-25 -> Sunday, 2012-09-02 = 0
Monday, 2012-08-27 -> Monday, 2012-08-27 = 0
Monday, 2012-08-27 -> Tuesday, 2012-08-28 = 1
Monday, 2012-08-27 -> Wednesday, 2012-08-29 = 2
Monday, 2012-08-27 -> Thursday, 2012-08-30 = 3
Monday, 2012-08-27 -> Friday, 2012-08-31 = 4
Monday, 2012-08-27 -> Saturday, 2012-09-01 = 5
Monday, 2012-08-27 -> Sunday, 2012-09-02 = 5

回答by Tom H

Since you will need to track holidays somewhere, a Calendar table seems appropriate:

由于您需要在某处跟踪假期，因此日历表似乎很合适：

CREATE TABLE Calendar
(
     calendar_date     DATETIME     NOT NULL,
     is_holiday        BIT          NOT NULL,
     is_weekend        BIT          NOT NULL,
     CONSTRAINT PK_Calendar PRIMARY KEY CLUSTERED (calendar_date)
)

You of course need to populate it with all dates for whatever time period you might ever work with in your application. Since there are only 365 (or 366) days in a year, going from 1900 to 2100 isn't a big deal. Just make sure that you load it with all dates, not just the holidays.

您当然需要使用您可能在应用程序中使用的任何时间段的所有日期填充它。由于一年只有 365（或 366）天，所以从 1900 到 2100 没什么大不了的。只需确保加载所有日期，而不仅仅是假期。

At that point queries like the one that you need become trivial:

那时，您需要的查询变得微不足道：

SELECT
     COUNT(*)
FROM
     Calendar
WHERE
     calendar_date BETWEEN '2009-01-01' AND '2009-10-01' AND
     is_holiday = 0 AND
     is_weekend = 0

Caveat: I work mostly with MS SQL and haven't worked with MySQL in a long time, so you may need to tweak the above. For example, I don't even remember if MySQL has the BIT datatype.

警告：我主要使用 MS SQL，并且很长时间没有使用 MySQL，因此您可能需要调整上述内容。例如，我什至不记得 MySQL 是否有 BIT 数据类型。

回答by Caveman

Could the proposed strings be wrong?

建议的字符串可能是错误的吗？

DATEDIFF(from, to) excludes 'to'. In the same way so should this string:

DATEDIFF(from, to) 不包括'to'。这个字符串也应该以同样的方式：

Monday -> friday = {Mon, Tu, Wed, Th} = 4

星期一 -> 星期五 = {Mon, Tu, Wed, Th} = 4

Monday -> Saturday = {Mon, Tu, Wed, Th, Fri} = 5

周一 -> 周六 = {周一、周二、周三、周四、周五} = 5

Tuesday -> Monday = {Tu, Wed, Th, Fri, skip Sat, skip Sun, Mon is excluded} = 4

星期二 -> 星期一 = {Tu, Wed, Th, Fri, 跳过星期六，跳过星期日，排除星期一} = 4

and so on

等等

Proposed Matrix:

建议矩阵：

 | M T W T F S S
-|--------------
M| 0 1 2 3 4 5 5
T| 4 0 1 2 3 4 4
W| 3 4 0 1 2 3 3
T| 2 3 4 0 1 2 2
F| 1 2 3 4 0 1 1
S| 0 1 2 3 4 0 0
S| 0 1 2 3 4 5 0

String: '0123455401234434012332340122123401101234000123450'

字符串：'0123455401234434012332340122123401101234000123450'

Am i missing something here? :)

我在这里错过了什么吗？:)

回答by Paulo Bueno

Just for futher reference. None of the above worked for me but a modified version of @Jeff Kooser:

仅供进一步参考。以上都不对我有用，而是@Jeff Kooser的修改版本：

SELECT (DATEDIFF(date_end, date_start)) -
        ((WEEK(date_end) - WEEK(date_start)) * 2) -
        (case when weekday(date_end) = 6 then 1 else 0 end) -
        (case when weekday(date_start) = 5 then 1 else 0 end) -
        (SELECT COUNT(*) FROM holidays WHERE holiday>=date_start and holiday<=data_end)

回答by Jeff Kooser

Given the first day of a month, this will return the number of weekdays within that month. In MySQL. Without a stored procedure.

给定一个月的第一天，这将返回该月内的工作日数。在 MySQL 中。没有存储过程。

SELECT (DATEDIFF(LAST_DAY(?),?) + 1) - 
    ((WEEK(LAST_DAY(?)) - WEEK(?)) * 2) -
    (case when weekday(?) = 6 then 1 else 0 end) - 
    (case when weekday(LAST_DAY(?)) = 5 then 1 else 0 end)

回答by yaronyogev

Based on the function above by Yada, here's a slight variation on the subject, which calculates work days left from the current date(not including), till the target date. It also handles the different weekend days in Israel :-) Note that this will produce a negative result if the target date is in the past(which is just what I wanted).

基于雅达上面的函数，这里有一个关于这个主题的轻微变化，它计算从当前日期（不包括）到目标日期的剩余工作日。它还处理以色列的不同周末：-) 请注意，如果目标日期在过去，这将产生负面结果（这正是我想要的）。

DELIMITER //
DROP FUNCTION IF EXISTS WORKDAYS_LEFT//

CREATE FUNCTION WORKDAYS_LEFT(target_date DATE, location char(2))
RETURNS INT
LANGUAGE SQL
DETERMINISTIC
BEGIN
  DECLARE start_date DATE;
  DECLARE end_date DATE;
  DECLARE check_date DATE;
  DECLARE diff INT;
  DECLARE extra_weekend_days INT;
  DECLARE weeks_diff INT;

  SET start_date = CURDATE();
  SET end_date = target_date;
  SET diff = DATEDIFF(end_date, start_date);
  SET weeks_diff = FLOOR(diff / 7);
  SET end_date = DATE_SUB(end_date, INTERVAL (weeks_diff * 7) DAY);
  SET check_date = DATE_ADD(start_date, INTERVAL 1 DAY);
  SET extra_weekend_days = 0;
  WHILE check_date <= end_date DO
    SET extra_weekend_days = extra_weekend_days +
      IF(DAYNAME(check_date) = 'Saturday', 1, 0) +
      IF(DAYNAME(check_date) = IF(location = 'IL','Friday', 'Sunday'), 1, 0);
    SET check_date = DATE_ADD(check_date, INTERVAL 1 DAY);
  END WHILE;

  RETURN diff - weeks_diff*2 - extra_weekend_days;
END//

DELIMITER ;

回答by jeeva

This query easily returns the number of working days between two dates exclude weekends:

此查询轻松返回两个日期之间的工作日数，不包括周末：

select datediff('2016-06-19','2016-06-01') - (floor(datediff('2016-06-19','2016-06-01')/6) + floor(datediff('2016-06-19','2016-06-01')/7));

回答by aastha agrrawal

SELECT  5* (DATEDIFF(u.EndDate, u.StartDate) DIV 7) + MID('1234555512344445123333451222234511112345001234550', 7 * WEEKDAY(u.StartDate) + WEEKDAY(u.EndDate) + 1, 1)

This is when you want to consider the following cases:

这是您要考虑以下情况的时候：

1) if startdate = enddate, duration = 1 and likewise..

1) 如果开始日期 = 结束日期，持续时间 = 1，同样..

I calculated the string using the logic mentioned in the most voted answer and got results as I needed.

我使用投票最多的答案中提到的逻辑计算了字符串，并根据需要得到了结果。

回答by jeffery_the_wind

OK Boys and Girls, I've got obviously the best solution, here is a simple select statement to get number of weekdays between 2 dates.

好的男孩和女孩，我显然有最好的解决方案，这里有一个简单的选择语句来获取两个日期之间的工作日数。

select 
    FLOOR(DATEDIFF(later_date, earlier_date) / 7) * 5 +  
    least(DATEDIFF(later_date, earlier_date) % 7, 5) + 
    if(weekday(later_date) < weekday(earlier_date), -2, 0);

A SIMPLE EXPLANATION

一个简单的解释

1. get number of complete weeks and multiply by 5 weekdays
2. get the number of days in the piece of leftover week
3. if the leftover week goes across a weekend, subtract the 2 weekend days

1. 获取完整周数并乘以 5 个工作日
2. 获取剩余一周的天数
3. 如果剩余的一周跨过周末，则减去 2 个周末天数