您如何从date_sub中排除周末?

问题描述：

我正在尝试使用date_sub减去当前日期-11天.我想排除周末..这是我到目前为止的情况:

I am trying to use date_sub to subtract the current date - 11 days. I would like to exclude weekends.. this is what I have so far:

DATE_SUB(now(), INTERVAL 11 day)

不确定如何排除周末...我们将不胜感激.

not to sure how to exclude weekends... any help is appreciated.

答

这个问题是关于减去工作日的.假设周末是周六至周日，我们可以编写如下解决方案:

This question is about subtracting working days. Assuming that weekend is Saturday-Sunday, we can write the solution as follows:

我们知道:

每个星期有5个工作日.
因此，
- num_of_weeks = floor(@num_working_days / 5)
- delta_days = @num_working_days % 5
- Every full week has 5 working days.
- Thus,
  - num_of_weeks = floor(@num_working_days / 5)
  - delta_days = @num_working_days % 5
  因此，第一个近似值可能是:
  
  So, a first approximation could be:
```
SET @num_working_days = 4; -- pick any integer
SET @num_days = 7 * FLOOR(@num_working_days / 5) - @num_working_days % 5;     
SELECT DATE_SUB(NOW(), INTERVAL @num_days DAY)
```
  但是，在以下情况和类似情况下，此方法将无效:
  
  如果今天是Monday而num_working_days % 5是1，则上面的内容会错误地给您Sunday，而应给您Friday.
  
  if today is Monday and num_working_days % 5 is 1, the above will errornously give you Sunday, when it should give you Friday.
  
  通常，如果发生以下情况，它将失败:
  
  Generally, it will fail if:
```
WEEKDAY(NOW()) - @num_working_days % 5 < 0
```
  为此，只要满足此条件，就必须再减去2天.
  
  To account for that, an additional 2 days must be subtracted whenever this condition is met.
  - overflow_days = 2 * (WEEKDAY(NOW()) - @num_working_days % 5 < 0)
  因此，第二个近似值是:
```
SET @num_working_days = 4;
SET @overflow_days = 2 * (WEEKDAY(NOW()) - @num_working_days % 5 < 0)
SET @num_days = 7 * FLOOR(@num_working_days / 5) - @num_working_days % 5;

SELECT DATE_SUB(NOW(), INTERVAL @num_days DAY)
```
  最后，
  
  只要now()不在week-end日，此功能将起作用.对于这种情况，您需要将上式中的now()替换为上一个周末的日期:
  
  Finally,
  
  This will work as long as now() is not in a week-end day. For that case, you'd need to replace now() in the above formula with the previous week-ending date:
  - weekend_correction = DATE_SUB(NOW(), INTERVAL WEEKDAY(NOW()) % 5 DAY)
  这会导致可怕的外观，但可以正常工作:
  
  Which leads to the horrible looking but fully working:
```
SET @num_working_days = 4;
SET @weekend_correction = DATE_SUB(NOW(), INTERVAL WEEKDAY(NOW()) % 5 DAY);
SET @overflow_days = 2 * (WEEKDAY(@weekend_correction) - @num_working_days % 5 < 0);
SET @num_days = 7 * FLOOR(@num_working_days / 5) - @num_working_days % 5;

SELECT DATE_SUB(@weekend_correction, INTERVAL @num_days DAY); 
```
  现在，在生产环境中，建议您在MySQL服务器上创建一个函数来封装此逻辑，并在需要减去工作日时可以调用此函数.

您如何从date_sub中排除周末?

最后，

Finally,

相关推荐