MySQL函数查找两个日期之间的工作日数 [英] MySQL function to find the number of working days between two dates

问题描述

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

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

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

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

推荐答案

这个表达式 -

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

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

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

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

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.

数字串的构造如下.创建一个表开始日和结束日，行必须从星期一(WEEKDAY0) 并且列也必须从星期一开始.填写从左上角到右下角的对角线全为 0(即有 0周一至周一、周二至周二等之间的工作日).每天从对角线开始(必须始终为 0)并填写右侧的列，一次一天.如果你降落在周末(非工作日)栏，工作日数不会改变，它是从左边携带的.否则，数工作日增加一.当你到达终点时行循环回到同一行的开头并继续直到您再次到达对角线.然后继续下一行.

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.

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

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

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

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

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

Please let me know if you find any bugs.

-编辑改进的表格:

 | 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

改进的字符串:'0123444401233334012222340111123400001234000123440'

improved string: '0123444401233334012222340111123400001234000123440'

改进的表达:

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

这篇关于MySQL函数查找两个日期之间的工作日数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！