mysql-function计算两个日期之间的天数，不包括周末 [英] mysql-function to count days between 2 dates excluding weekends

问题描述

我搜索了很多例子，很好的例子:

1. 计算两个日期之间的天数，不包括周末(仅适用于MySQL)

2. 如何计算日期差异，不包括MySQL中的周末和假期

3. 计算以下两个日期之间的差异SQL，不包括周末

但是没有得到最有前途的解决方案，因此我可以在mysql函数中使用它来查询数十万行.

这是一个非常新的概念，但不适用于@start_date = '2013-08-03' , @end_date = '2013-08-21'预期的ans:13，仅给出12的输入

SELECT 5 * (DATEDIFF(@end_date, @start_date) DIV 7) + MID('0123444401233334012222340111123400012345001234550', 7 * WEEKDAY(@start_date) + WEEKDAY(@end_date) + 1, 1);

所以我试图自己做-

Concept : 
Input : 1. period_from_date  - from date
        2. period_to_date    - to date
        3. days_to_exclude   - mapping : S M T W TH F Sat   =>  2^0 + 2^6
          (sat and sun to exclude)       ^ ^ ^ ^ ^  ^  ^
                                         0 1 2 3 4  5  6

---

DELIMITER $$

USE `db_name`$$

DROP FUNCTION IF EXISTS `FUNC_CALC_TOTAL_WEEKDAYS`$$

CREATE DEFINER=`name`@`%` FUNCTION `FUNC_CALC_TOTAL_WEEKDAYS`( period_from_date DATE, period_to_date DATE, days_to_exclude INT ) RETURNS INT(11)
BEGIN

DECLARE period_total_num_days      INT DEFAULT 0;
DECLARE period_total_working_days  INT DEFAULT 0;
DECLARE period_extra_days          INT DEFAULT 0;
DECLARE period_complete_weeks      INT DEFAULT 0;
DECLARE extra_days_start_date      DATE DEFAULT '0000-00-00';
DECLARE num_days_to_exclude        INT DEFAULT 0;
DECLARE start_counter_frm          INT DEFAULT 0;
DECLARE end_counter_to             INT DEFAULT 6;
DECLARE temp_var                   INT DEFAULT 0;

# if no day to exclude return date-diff only
IF days_to_exclude = 0 THEN
    RETURN DATEDIFF( period_to_date, period_from_date ) + 1 ;
END IF;

# get total no of days to exclude
WHILE start_counter_frm <= end_counter_to  DO
   SET temp_var = POW(2,start_counter_frm) ;
   IF (temp_var  & days_to_exclude) = temp_var  THEN
            SET num_days_to_exclude = num_days_to_exclude + 1;
   END IF;
 SET start_counter_frm = start_counter_frm + 1;
END WHILE;

# Get period days count
SET period_total_num_days       = DATEDIFF( period_to_date, period_from_date ) + 1 ;
SET period_complete_weeks       = FLOOR( period_total_num_days /7 );
SET period_extra_days           = period_total_num_days  - ( period_complete_weeks * 7 );
SET period_total_working_days   = period_complete_weeks * (7 - num_days_to_exclude);
SET extra_days_start_date       = DATE_SUB(period_to_date,INTERVAL period_extra_days DAY);

# get total working days from the left days
WHILE period_extra_days > 0 DO
    SET temp_var = DAYOFWEEK(period_to_date) -1;

    IF POW(2,temp_var) & days_to_exclude != POW(2,temp_var) THEN
        SET period_total_working_days = period_total_working_days +1;
    END IF;

    SET period_to_date = DATE_SUB(period_to_date,INTERVAL 1 DAY);
    SET period_extra_days = period_extra_days -1;

END WHILE; 

RETURN period_total_working_days;
END$$

DELIMITER ;

请让我知道失败的地方.欢迎提出任何建议和意见.

解决方案

更新:如果您只需要两个日期之间的多个工作日，就可以像这样

CREATE FUNCTION TOTAL_WEEKDAYS(date1 DATE, date2 DATE)
RETURNS INT
RETURN ABS(DATEDIFF(date2, date1)) + 1
     - ABS(DATEDIFF(ADDDATE(date2, INTERVAL 1 - DAYOFWEEK(date2) DAY),
                    ADDDATE(date1, INTERVAL 1 - DAYOFWEEK(date1) DAY))) / 7 * 2
     - (DAYOFWEEK(IF(date1 < date2, date1, date2)) = 1)
     - (DAYOFWEEK(IF(date1 > date2, date1, date2)) = 7);

注意:如果您切换开始日期date1和结束日期date2，该功能仍将起作用.

样品用量:

SELECT TOTAL_WEEKDAYS('2013-08-03', '2013-08-21') weekdays1,
       TOTAL_WEEKDAYS('2013-08-21', '2013-08-03') weekdays2;

输出:

| WEEKDAYS1 | WEEKDAYS2 |
-------------------------
|        13 |        13 |

这里是 DBFiddle 演示

I've searched through many examples , good ones I got :

1. Count days between two dates, excluding weekends (MySQL only)

2. How to count date difference excluding weekend and holidays in MySQL

3. Calculate diffference between 2 dates in SQL, excluding weekend days

but didn't get most promising solution , so that i can use in my mysql-function for quering lakhs of rows.

This one was very new concept , but didn't worked for inputs like @start_date = '2013-08-03' , @end_date = '2013-08-21' Expected ans : 13 , its giving only 12,

SELECT 5 * (DATEDIFF(@end_date, @start_date) DIV 7) + MID('0123444401233334012222340111123400012345001234550', 7 * WEEKDAY(@start_date) + WEEKDAY(@end_date) + 1, 1);

So i'did tried to make it by myself -

Concept : 
Input : 1. period_from_date  - from date
        2. period_to_date    - to date
        3. days_to_exclude   - mapping : S M T W TH F Sat   =>  2^0 + 2^6
          (sat and sun to exclude)       ^ ^ ^ ^ ^  ^  ^
                                         0 1 2 3 4  5  6

---

DELIMITER $$

USE `db_name`$$

DROP FUNCTION IF EXISTS `FUNC_CALC_TOTAL_WEEKDAYS`$$

CREATE DEFINER=`name`@`%` FUNCTION `FUNC_CALC_TOTAL_WEEKDAYS`( period_from_date DATE, period_to_date DATE, days_to_exclude INT ) RETURNS INT(11)
BEGIN

DECLARE period_total_num_days      INT DEFAULT 0;
DECLARE period_total_working_days  INT DEFAULT 0;
DECLARE period_extra_days          INT DEFAULT 0;
DECLARE period_complete_weeks      INT DEFAULT 0;
DECLARE extra_days_start_date      DATE DEFAULT '0000-00-00';
DECLARE num_days_to_exclude        INT DEFAULT 0;
DECLARE start_counter_frm          INT DEFAULT 0;
DECLARE end_counter_to             INT DEFAULT 6;
DECLARE temp_var                   INT DEFAULT 0;

# if no day to exclude return date-diff only
IF days_to_exclude = 0 THEN
    RETURN DATEDIFF( period_to_date, period_from_date ) + 1 ;
END IF;

# get total no of days to exclude
WHILE start_counter_frm <= end_counter_to  DO
   SET temp_var = POW(2,start_counter_frm) ;
   IF (temp_var  & days_to_exclude) = temp_var  THEN
            SET num_days_to_exclude = num_days_to_exclude + 1;
   END IF;
 SET start_counter_frm = start_counter_frm + 1;
END WHILE;

# Get period days count
SET period_total_num_days       = DATEDIFF( period_to_date, period_from_date ) + 1 ;
SET period_complete_weeks       = FLOOR( period_total_num_days /7 );
SET period_extra_days           = period_total_num_days  - ( period_complete_weeks * 7 );
SET period_total_working_days   = period_complete_weeks * (7 - num_days_to_exclude);
SET extra_days_start_date       = DATE_SUB(period_to_date,INTERVAL period_extra_days DAY);

# get total working days from the left days
WHILE period_extra_days > 0 DO
    SET temp_var = DAYOFWEEK(period_to_date) -1;

    IF POW(2,temp_var) & days_to_exclude != POW(2,temp_var) THEN
        SET period_total_working_days = period_total_working_days +1;
    END IF;

    SET period_to_date = DATE_SUB(period_to_date,INTERVAL 1 DAY);
    SET period_extra_days = period_extra_days -1;

END WHILE; 

RETURN period_total_working_days;
END$$

DELIMITER ;

Please let me know the holes where this would fail.Open to any suggestions and comments.

解决方案

UPDATED: If you just need a number of weekdays between two dates you can get it like this

CREATE FUNCTION TOTAL_WEEKDAYS(date1 DATE, date2 DATE)
RETURNS INT
RETURN ABS(DATEDIFF(date2, date1)) + 1
     - ABS(DATEDIFF(ADDDATE(date2, INTERVAL 1 - DAYOFWEEK(date2) DAY),
                    ADDDATE(date1, INTERVAL 1 - DAYOFWEEK(date1) DAY))) / 7 * 2
     - (DAYOFWEEK(IF(date1 < date2, date1, date2)) = 1)
     - (DAYOFWEEK(IF(date1 > date2, date1, date2)) = 7);

Note: The function will still work if you switch start date1 and end date2 dates.

Sample usage:

SELECT TOTAL_WEEKDAYS('2013-08-03', '2013-08-21') weekdays1,
       TOTAL_WEEKDAYS('2013-08-21', '2013-08-03') weekdays2;

Output:

| WEEKDAYS1 | WEEKDAYS2 |
-------------------------
|        13 |        13 |

Here is DBFiddle demo

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