您如何从date_sub中排除周末? [英] How do you exclude weekends from a 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中排除周末?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！