Comparaison des plages de dates

Question

Dans MySQL, si je dispose d'une liste de plages de dates (début et fin de plage), par exemple

10/06/1983 to 14/06/1983
15/07/1983 to 16/07/1983
18/07/1983 to 18/07/1983

Et je veux vérifier si une autre plage de dates contient TOUTES les plages déjà présentes dans la liste, comment faire ?

par exemple

06/06/1983 to 18/06/1983 = IN LIST
10/06/1983 to 11/06/1983 = IN LIST
14/07/1983 to 14/07/1983 = NOT IN LIST

Answer 1

C'est un problème classique, et c'est en fait plus facile si on inverse la logique.

Laissez-moi vous donner un exemple.

Je vais afficher une période de temps ici, et toutes les différentes variations d'autres périodes qui se chevauchent d'une manière ou d'une autre.

           |-------------------|          compare to this one
               |---------|                contained within
           |----------|                   contained within, equal start
                   |-----------|          contained within, equal end
           |-------------------|          contained within, equal start+end
     |------------|                       not fully contained, overlaps start
                   |---------------|      not fully contained, overlaps end
     |-------------------------|          overlaps start, bigger
           |-----------------------|      overlaps end, bigger
     |------------------------------|     overlaps entire period

d'un autre côté, laissez-moi afficher tous ceux qui ne se chevauchent pas :

           |-------------------|          compare to this one
     |---|                                ends before
                                 |---|    starts after

Donc si vous réduisez simplement la comparaison à :

starts after end
ends before start

puis vous trouverez toutes celles qui ne se chevauchent pas, et enfin vous trouverez toutes les périodes non concordantes.

Pour votre dernier exemple NOT IN LIST, vous pouvez voir qu'il correspond à ces deux règles.

Vous devrez décider si les périodes suivantes sont DANS ou HORS de vos plages :

           |-------------|
   |-------|                       equal end with start of comparison period
                         |-----|   equal start with end of comparison period

Si votre table possède des colonnes appelées range_end et range_start, voici un langage SQL simple pour récupérer toutes les lignes correspondantes :

SELECT *
FROM periods
WHERE NOT (range_start > @check_period_end
           OR range_end < @check_period_start)

Notez le PAS là-dedans. Puisque les deux règles simples trouvent toutes les non assortie les rangs, un simple NOT l'inversera pour dire : si ce n'est pas une des rangées non correspondantes, ce doit être une des rangées correspondantes. .

En appliquant une simple logique d'inversion ici pour se débarrasser du NOT, on obtient.. :

SELECT *
FROM periods
WHERE range_start <= @check_period_end
      AND range_end >= @check_period_start

Answer 2

En prenant l'exemple de la plage du 06/06/1983 au 18/06/1983 et en supposant que vous avez des colonnes appelées commencer y fin pour vos plages, vous pourriez utiliser une clause comme celle-ci

where ('1983-06-06' <= end) and ('1983-06-18' >= start)

c'est-à-dire vérifier que le début de votre plage de test est avant la fin de la plage de la base de données, et que la fin de votre plage de test est après ou sur le début de la plage de la base de données.

Answer 3

Si votre SGBDR prend en charge la fonction OVERLAP(), cela devient trivial - pas besoin de solutions maison. (Dans Oracle, cela fonctionne apparemment mais n'est pas documenté).

Answer 4

Dans vos résultats attendus, vous dites

06/06/1983 au 18/06/1983 = EN LISTE

Cependant, cette période ne contient ni n'est contenue par aucune des périodes de votre tableau (pas liste !) de périodes. Elle chevauche cependant la période du 10/06/1983 au 14/06/1983.

Vous pouvez trouver le livre de Snodgrass ( http://www.cs.arizona.edu/people/rts/tdbbook.pdf ) utile : il est antérieur à mysql mais la notion de temps n'a pas changé ;-)

Answer 5

J'ai créé une fonction pour traiter ce problème dans MySQL. Il suffit de convertir les dates en secondes avant de les utiliser.

DELIMITER ;;

CREATE FUNCTION overlap_interval(x INT,y INT,a INT,b INT)
RETURNS INTEGER DETERMINISTIC
BEGIN
DECLARE
    overlap_amount INTEGER;
    IF (((x <= a) AND (a < y)) OR ((x < b) AND (b <= y)) OR (a < x AND y < b)) THEN
        IF (x < a) THEN
            IF (y < b) THEN
                SET overlap_amount = y - a;
            ELSE
                SET overlap_amount = b - a;
            END IF;
        ELSE
            IF (y < b) THEN
                SET overlap_amount = y - x;
            ELSE
                SET overlap_amount = b - x;
            END IF;
        END IF;
    ELSE
        SET overlap_amount = 0;
    END IF;
    RETURN overlap_amount;
END ;;

DELIMITER ;