mysql querying data that INSIDE two date's column

Question

So i have a table with structure:

events
----------
id
name
start_date
end_date

and i need to build a calendar preview for this events. so user can choose the date range that they want to show.

Let's say that we have some data like this :

1 | event A | 01-02-2022 | 10-02-2022

So there is a request with two parameters, from and to parameter so that user can choose what dates that they want to show on calendar (frontend) and it will be requested to backend

There are three cases:

user choose from 01-02-2022 to 28-02-2022 (monthly)
user choose from 06-02-2022 to 12-02-2022 (weekly)
user choose from 20-01-2022 to 03-02-2022 (custom)

I want these three cases will show event with id 1

The thing is, if I use conventional using between query filter, it will generate a query like this:

1. select * from events where start_date between '01-02-2022' and '28-02-2022'
2. select * from events where start_date between '06-02-2022' and '12-02-2022'
3. select * from events where start_date between '20-01-2022' and '03-02-2022'

For the first case, it will show event with id 1 because start_date satisfied between query.

But for the second and third case, its not because start_date is not satisfied between query.

What I want is that for second and third case, my query would return the event with id 1 too because between 06-02-2022 to 12-02-2022 and 20-01-2022 to 03-02-2022 has some dates that within the event 1 start and end range

Could anyone give me an example query that will satisfy?

kRiZ · Accepted Answer · 2022-02-21 05:57:46Z

2

You are not getting the last two cases correct because you are not checking for the end_date.

Try including the end_date as well in your condition:

select * from events 
where (start_date between '01-02-2022' and '28-02-2022') or (end_date between '01-02-2022' and '28-02-2022')

answered Feb 21, 2022 at 5:57

kRiZ

2,3164 gold badges30 silver badges39 bronze badges

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

1 Comment

Gery Ruslandi Over a year ago

Eh haha how come I didn't realize this logic hahaha. Thankyou kRiz, its really solved my issue

Collectives™ on Stack Overflow

mysql querying data that INSIDE two date's column

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related