How do I get the difference between dates on business days in Mysql?

Question

I would like to know if it is possible to calculate the difference between 2 dates by counting working days.

In that reply there is even a solution, but from what I saw, the check is only for weekends, if there is a holiday in the middle of the week, the deadline will not be correct.

At the moment I am calculating using only DATEDIFF, that returns the difference in run-day numbers.

SELECT DATEDIFF(CURDATE(), '2017-12-20');

The result I would like to get would be the amount of days between the date A and date B excluding weekends and holidays. For example, if from a Tuesday to the Tuesday of the following week had a holiday on Thursday, the result should be 3 working days (Wednesday, Friday, Monday).

Answer 1

I went through a problem like this once, we solved it like this:

Table of dias_uteis:

 CREATE TABLE dias_uteis(
id INT NOT NULL PRIMARY KEY AUTO_INCREMENT, 
    data_util DATE
);

I made a procedure where I spend the year and she inserts for me all dates except sábado e domingo:

   --WARNING! ERRORS ENCOUNTERED DURING SQL PARSING!
DROP PROCEDURE

IF EXISTS sp_insere_datas_ano;
    DELIMITER |

CREATE PROCEDURE sp_insere_datas_ano (p_ano INT)

BEGIN
    DECLARE v_date DATE DEFAULT CONCAT (
        p_ano
        ,'-01-01'
        );
    DECLARE v_qtd_dias INT DEFAULT 365;
    DECLARE v_cont INT DEFAULT 1;

    WHILE v_cont < v_qtd_dias DO
        -- AQUI VOCÊ NÃO INSERE SABADOS E DOMINGOS
        IF DAYOFWEEK(v_date) != 1
            AND DAYOFWEEK(v_date) != 7 THEN
            INSERT INTO dias_uteis (data_util) VALUE (v_date);
END

IF ;
    SET v_date = DATE_ADD(v_date, INTERVAL + 1 DAY);
SET v_cont = v_cont + 1;END

WHILE ;END |
    DELIMITER;
        -- CALL sp_insere_datas_ano(2017);

We take the calendar of the year and remove the holidays:

DELETE
FROM dias_uteis
WHERE data_util = [data_deletar];

To count the working days only carry out the consultation:

SELECT count(*)
FROM dias_uteis
WHERE data_util BETWEEN [inicio]
        AND [fim]

An extra case would be municipal holidays, which we could solve as follows:

CREATE TABLE municipios (
    id INT NOT NULL PRIMARY KEY AUTO_INCREMENT
    ,nome VARCHAR(100)
    );

CREATE TABLE municipio_dia_ignorar (
    id INT (11) NOT NULL AUTO_INCREMENT
    ,id_dia_util INT (11) DEFAULT NULL
    ,id_municipio INT (11) DEFAULT NULL
    ,PRIMARY KEY (id)
    ,KEY fk_municipio_dia_ignorar_dia_util(id_dia_util)
    ,KEY fk_municipio_dia_ignorar_municipio(id_municipio)
    );

Let’s insert the city of Curitiba

INSERT INTO municipios (nome)
VALUES ('Curitiba');

Let us consider what day 07/09/2017 is a national holiday and that Curitiba has as municipal holiday day 08/09/2017, we would delete day 7 from the day table and insert day 8 into the skip table:

DELETE
FROM dias_uteis
WHERE data_util = '2017-09-07';

INSERT INTO municipio_dia_ignorar (
    id_dia_util
    ,id_municipio
    )
SELECT id
    ,1
FROM dias_uteis
WHERE dias_uteis.data_util = '2017-09-08';

We check the amount of working days from day 1 to day 10:

SELECT COUNT(*)
FROM dias_uteis d
LEFT JOIN municipio_dia_ignorar mi ON mi.id_dia_util = d.id
    AND mi.id_municipio = 1
WHERE d.data_util BETWEEN '2017-09-01'
        AND '2017-09-10'
    AND mi.id IS NULL

And the return would be 4 days:

01/09
04/09
05/09
06/09

Answer 2

Taking advantage of the friend’s idea, I increased the national holidays in the procedure itself.

Holiday basis: https://www.anbima.com.br/feriados/arqs/feriados_nacionais.xls

I put an event to call once a year and it already resolves for me.

In the case of municipal really do not see a way other than with an external job.

DELIMITER $$
DROP PROCEDURE IF EXISTS `stpBusinessDay`$$
CREATE PROCEDURE `stpBusinessDay`(_year YEAR)
BEGIN

    DECLARE _i INT DEFAULT 1;
    DECLARE _limit INT DEFAULT 365;
    DECLARE _dt DATE DEFAULT CONCAT (_year,'-01-01');

    TRUNCATE TABLE tbBusinessDay;
    
    WHILE _i < _limit DO
        IF DAYOFWEEK(_dt) NOT IN(1,7) THEN
            INSERT INTO `tbBusinessDay` VALUE (_dt);
        END IF ;
        SET _dt = DATE_ADD(_dt, INTERVAL + 1 DAY);
        SET _i = _i + 1;
    END WHILE;

    WITH dayOff AS (
        SELECT CONCAT (_year,'-01-01') AS dt -- Confraternização Universal
        UNION
        SELECT CONCAT (_year,'-02-24') AS dt -- Carnaval
        UNION
        SELECT CONCAT (_year,'-02-25') AS dt -- Carnaval
        UNION
        SELECT CONCAT (_year,'-04-10') AS dt -- Paixão de Cristo
        UNION
        SELECT CONCAT (_year,'-04-21') AS dt -- Tiradentes
        UNION
        SELECT CONCAT (_year,'-05-01') AS dt -- Dia do Trabalho
        UNION
        SELECT CONCAT (_year,'-06-11') AS dt -- Corpus Christi
        UNION
        SELECT CONCAT (_year,'-09-07') AS dt -- Independência do Brasil
        UNION
        SELECT CONCAT (_year,'-10-12') AS dt -- Nossa Sr.a Aparecida - Padroeira do Brasil
        UNION
        SELECT CONCAT (_year,'-11-02') AS dt -- Finados
        UNION
        SELECT CONCAT (_year,'-11-15') AS dt -- Proclamação da República
        UNION   
        SELECT CONCAT (_year,'-12-25') AS dt -- Natal
    )
    DELETE A FROM `tbBusinessDay` A
    INNER JOIN dayOff B ON (A.dt = B.dt);
    END$$
DELIMITER ;

Answer 3

It could be something like this:

SELECT *
FROM minhatabela
WHERE date BETWEEN @inicio AND @fim
AND WEEKDAY(date) < 5

This, of course, does not solve the issue of holidays.