计算两个日期之间的天数，不包括周末(仅限 MySQL) [英] Count days between two dates, excluding weekends (MySQL only)

问题描述

我需要计算 MySQL 中两个日期之间的差异(以天为单位)，不包括周末(周六和周日).即天数减去两者之间周六和周日的天数.

I need to calculate the difference (in days) between two dates in MySQL excluding weekends (Saturday and Sunday). That is, the difference in days minus the number of Saturday and Sunday in between.

目前，我只是使用以下方法计算天数:

At the moment, I simply count the days using:

SELECT DATEDIFF('2012-03-18', '2012-03-01')

这个返回17，但我想排除周末，所以我想要12(因为第3和第4、第10和第11和第17天是周末).

This return 17, but I want to exclude weekends, so I want 12 (because the 3rd and 4th, 10th and 11th and 17th are weekends days).

我不知道从哪里开始.我知道 WEEKDAY() 函数和所有相关的函数，但我不知道如何在这种情况下使用它们.

I do not know where to start. I know about the WEEKDAY() function and all related ones, but I do not know how to use them in this context.

推荐答案

插图:

mtwtfSSmtwtfSS
  123456712345   one week plus 5 days, you can remove whole weeks safely
  12345-------   you can analyze partial week's days at start date
  -------12345   or at ( end date - partial days )

伪代码:

@S          = start date
@E          = end date, not inclusive
@full_weeks = floor( ( @E-@S ) / 7)
@days       = (@E-@S) - @full_weeks*7   OR (@E-@S) % 7

SELECT
  @full_weeks*5 -- not saturday+sunday
 +IF( @days >= 1 AND weekday( S+0 )<=4, 1, 0 )
 +IF( @days >= 2 AND weekday( S+1 )<=4, 1, 0 )
 +IF( @days >= 3 AND weekday( S+2 )<=4, 1, 0 )
 +IF( @days >= 4 AND weekday( S+3 )<=4, 1, 0 )
 +IF( @days >= 5 AND weekday( S+4 )<=4, 1, 0 )
 +IF( @days >= 6 AND weekday( S+5 )<=4, 1, 0 )
 -- days always less than 7 days

这篇关于计算两个日期之间的天数，不包括周末(仅限 MySQL)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！