How do I get the difference between working days in MySql?

Question

How do I get the difference between working days in MySql?

Navigation

#1 by (3 votes)
#2 by (1 votes)

1

I wonder if it is possible to calculate the difference between 2 dates by counting business days.

In this answer until there is a solution, but from what I saw , the check is only for weekends, if there is a midweek holiday, the deadline will not be correct.

I'm currently calculating using only DATEDIFF , which returns the difference in number of days running.

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

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

mysql

asked by anonymous 28.11.2017 / 11:46

2 answers

1

It may look something like this:

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

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

28.11.2017 / 15:34

How to declare an interface attribute? Project class library does not open. Gives its .csproj error

score 3 · Accepted Answer

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

dias_uteis table:

 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 it 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 took the year calendar and removed the holidays:

DELETE
FROM dias_uteis
WHERE data_util = [data_deletar];

To count the useful days just make the query:

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 enter the city of Curitiba

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

Consider that day 07/09/2017 is national holiday and that Curitiba has as a public holiday day 08/09/2017 , we would delete day 7 of the days table and insert day 8 into the ignore 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 checked the number of useful days from day 1 to 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: