Comparaison de plages de dates

En MySQL, si j’ai 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 des plages déjà présentes dans la liste, comment ferais-je cela?

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 

C’est un problème classique, et c’est en fait plus facile si vous inversez la logique.

Laisse moi te donner un exemple.

Je posterai une période de temps ici, et toutes les différentes variations des autres périodes qui se chevauchent d’une certaine manière.

  |-------------------| 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é, permettez-moi de poster 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 

alors vous trouverez tous ceux qui ne se chevauchent pas, puis vous trouverez toutes les périodes sans correspondance.

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 IN ou HORS de vos gammes:

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

Si votre table a des colonnes appelées range_end et range_start, voici un simple SQL 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. Comme les deux règles simples trouvent toutes les lignes qui ne correspondent pas , un simple NOT l'inverse pour dire: si ce n'est pas l'une des lignes qui ne correspondent pas, elle doit être l'une des lignes correspondantes .

En appliquant une simple logique d'inversion ici pour se débarrasser du NOT et vous vous retrouverez avec:

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

En prenant votre exemple du 06/06/1983 au 18/06/1983 et en supposant que vous avez des colonnes appelées start et end pour vos plages, vous pouvez utiliser une clause comme celle-ci

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

c.-à-d. vérifier que le début de votre plage de test est avant la fin de la plage de la firebase database et que la fin de votre plage de test se situe après ou au début de la plage de la firebase database.

Si votre SGBDR supporte la fonction OVERLAP (), cela devient sortingvial – pas besoin de solutions internes. (Dans Oracle, cela fonctionne apparemment mais n’est pas documenté).

Dans vos résultats escomptés, vous dites

06/06/1983 au 18/06/1983 = IN LIST

Cependant, cette période ne contient ni ne figure dans aucune des périodes de votre tableau (pas dans la liste!) Des périodes. Il chevauche toutefois la période du 10/06/1983 au 14/06/1983.

Vous pouvez trouver le livre Snodgrass ( http://www.cs.arizona.edu/people/rts/tdbbook.pdf ) utile: il précède mysql mais le concept de temps n’a pas changé 😉

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

 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 ; 

Regardez dans l’exemple suivant. Cela vous sera utile.

  SELECT DISTINCT RelatedTo,CAST(NotificationContent as nvarchar(max)) as NotificationContent, ID, Url, NotificationPrefix, NotificationDate FROM NotificationMaster as nfm inner join NotificationSettingsSubscriptionLog as nfl on nfm.NotificationDate between nfl.LastSubscribedDate and isnull(nfl.LastUnSubscribedDate,GETDATE()) where ID not in(SELECT NotificationID from removednotificationsmaster where Userid=@userid) and nfl.UserId = @userid and nfl.RelatedSettingColumn = RelatedTo 

Essayez ceci sur MS SQL


 WITH date_range (calc_date) AS ( SELECT DATEADD(DAY, DATEDIFF(DAY, 0, [ending date]) - DATEDIFF(DAY, [start date], [ending date]), 0) UNION ALL SELECT DATEADD(DAY, 1, calc_date) FROM date_range WHERE DATEADD(DAY, 1, calc_date) <= [ending date]) SELECT P.[fieldstartdate], P.[fieldenddate] FROM date_range R JOIN [yourBaseTable] P on Convert(date, R.calc_date) BETWEEN convert(date, P.[fieldstartdate]) and convert(date, P.[fieldenddate]) GROUP BY P.[fieldstartdate], P.[fieldenddate]; 
 CREATE FUNCTION overlap_date(s DATE, e DATE, a DATE, b DATE) RETURNS BOOLEAN DETERMINISTIC RETURN s BETWEEN a AND b or e BETWEEN a and b or a BETWEEN s and e; 

Une autre méthode à l’aide de l’instruction SQL BETWEEN

Périodes incluses:

 SELECT * FROM periods WHERE @check_period_start BETWEEN range_start AND range_end AND @check_period_end BETWEEN range_start AND range_end 

Périodes exclues:

 SELECT * FROM periods WHERE (@check_period_start NOT BETWEEN range_start AND range_end OR @check_period_end NOT BETWEEN range_start AND range_end) 
 SELECT * FROM tabla a WHERE ( @Fini <= a.dFechaFin AND @Ffin >= a.dFechaIni ) AND ( (@Fini >= a.dFechaIni AND @Ffin <= a.dFechaFin) OR (@Fini >= a.dFechaIni AND @Ffin >= a.dFechaFin) OR (a.dFechaIni>=@Fini AND a.dFechaFin <=@Ffin) OR (a.dFechaIni>=@Fini AND a.dFechaFin >=@Ffin) )