Comparing date ranges

In MySQL, If I have a list of date ranges (range-start and range-end). e.g.

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

And I want to check if another date range contains ANY of the ranges already in the list, how would I do that?


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


Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

This is a classical problem, and it’s actually easier if you reverse the logic.

Let me give you an example.

I’ll post one period of time here, and all the different variations of other periods that overlap in some way.

           |-------------------|          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

on the other hand, let me post all those that doesn’t overlap:

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

So if you simple reduce the comparison to:

starts after end
ends before start

then you’ll find all those that doesn’t overlap, and then you’ll find all the non-matching periods.

For your final NOT IN LIST example, you can see that it matches those two rules.

You will need to decide wether the following periods are IN or OUTSIDE your ranges:

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

If your table has columns called range_end and range_start, here’s some simple SQL to retrieve all the matching rows:

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

Note the NOT in there. Since the two simple rules finds all the non-matching rows, a simple NOT will reverse it to say: if it’s not one of the non-matching rows, it has to be one of the matching ones.

Applying simple reversal logic here to get rid of the NOT and you’ll end up with:

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

Method 2

Taking your example range of 06/06/1983 to 18/06/1983 and assuming you have columns called start and end for your ranges, you could use a clause like this

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

i.e. check the start of your test range is before the end of the database range, and that the end of your test range is after or on the start of the database range.

Method 3

If your RDBMS supports the OVERLAP() function then this becomes trivial — no need for homegrown solutions. (In Oracle it apparantly works but is undocumented).

Method 4

In your expected results you say

06/06/1983 to 18/06/1983 = IN LIST

However, this period does not contain nor is contained by any of the periods in your table (not list!) of periods. It does, however, overlap the period 10/06/1983 to 14/06/1983.

You may find the Snodgrass book ( useful: it pre-dates mysql but the concept of time hasn’t changed 😉

Method 5

I created function to deal with this problem in MySQL. Just convert the dates to seconds before use.


CREATE FUNCTION overlap_interval(x INT,y INT,a INT,b INT)
    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;
                SET overlap_amount = b - a;
            END IF;
            IF (y < b) THEN
                SET overlap_amount = y - x;
                SET overlap_amount = b - x;
            END IF;
        END IF;
        SET overlap_amount = 0;
    END IF;
    RETURN overlap_amount;
END ;;


Method 6

Look into the following example. It will helpful for you.

    SELECT  DISTINCT RelatedTo,CAST(NotificationContent as nvarchar(max)) as NotificationContent,
                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 <a href="" class="__cf_email__" data-cfemail="13466076617a772e53666076617a77">[email protected]</a>) and  nfl.UserId = @userid and nfl.RelatedSettingColumn = RelatedTo

Method 7

Try This on MS SQL

WITH date_range (calc_date) AS (
SELECT DATEADD(DAY, DATEDIFF(DAY, 0, [ending date]) - DATEDIFF(DAY, [start date], [ending date]), 0)
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];

Method 8

CREATE FUNCTION overlap_date(s DATE, e DATE, a DATE, b DATE)
RETURN s BETWEEN a AND b or e BETWEEN a and b or  a BETWEEN s and e;

Method 9

Another method by using BETWEEN sql statement

Periods included :

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

Periods excluded :

FROM periods
WHERE (@check_period_start NOT BETWEEN range_start AND range_end
  OR @check_period_end NOT BETWEEN range_start AND range_end)

Method 10

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><a href="" class="__cf_email__" data-cfemail="e5d8a5a38c8b8c">[email protected]</a> AND a.dFechaFin <<a href="" class="__cf_email__" data-cfemail="0e334e48686760">[email protected]</a>) OR
(a.dFechaIni><a href="" class="__cf_email__" data-cfemail="360b76705f585f">[email protected]</a> AND a.dFechaFin ><a href="" class="__cf_email__" data-cfemail="6f522f29090601">[email protected]</a>) )

All methods was sourced from or, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

0 0 votes
Article Rating
Notify of

Inline Feedbacks
View all comments
Would love your thoughts, please comment.x