MYSQL: Filter with multiple dates and date range

Question

I have 2 dates to be filtered by 2 dates.

I have this data on my table

id  date_from    date_to     name
1   2018-10-11   2018-10-25  LeBron James
2   2018-10-11   2018-10-25  Stephen Curry
3   2018-10-26   2018-11-10  Kevin Durant

What I wanted to do is to filter it base on the date range given. So If I have the 2 dates(2018-10-01 to 2018-10-22). I will only see id 1 and 2, and if I have (2018-10-12 to 2018-10-31) I will show all data.

My sample code I used start and end for the range

 SELECT * FROM table WHERE (date_from BETWEEN @start AND @end) OR (date_to BETWEEN @start AND @end)

// The problem with this is that if no value found in range it will show me everything.

 SELECT * FROM table WHERE (date_from >= @start AND date_from <= @end) AND (date_to >= @start AND date_to <= @end)

// The problem - No data will appear

What should I do, I am confused with lots of dates.

Gordon Linoff · Accepted Answer · 2018-12-20 00:19:46Z

3

I think you want and overlap. If so:

SELECT t.*
FROM table
WHERE t.date_from <= @end AND
      t.date_to >= @start;

Two periods overlap if one starts before the second ends and vice versa.

answered Dec 20, 2018 at 0:19

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

sad saddest Over a year ago

What magic, It works. Thank you so much sir, and a happy holidays to you

Paul Maxwell · Accepted Answer · 2018-12-20 01:05:00Z

3

This might help clarify:

  @start    @end
s-E |        |        ignore (starts & ends before the period)

 s--|--------|-E      spans the period
  s-|---E    |        start before, but ends in, the period
    s----E   |        starts at beginning of the period, finishes before the period ends
    | s---E  |        starts and finished within the period
    |   s----E        starts within the period, finishes on last day of the period
    |     s--|-E      starts within the period but finishes after the period

    |        | s-E    ignore (starts & ends after the period)

 for those we want s is always <= @end
 for those we want E is always >= @start

It can seem counter-intuitive to compare starts to an end point or vice-versa, but as that small graphic attempt to display, it is the most consistent relationship.

answered Dec 20, 2018 at 1:05

Paul Maxwell

35.7k4 gold badges39 silver badges55 bronze badges

1 Comment

sad saddest Over a year ago

thank you for the clarification sir! happy holidays :)

Collectives™ on Stack Overflow

MYSQL: Filter with multiple dates and date range

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related