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:

1. user choose from 01-02-2022 to 28-02-2022 (monthly)
2. user choose from 06-02-2022 to 12-02-2022 (weekly)
3. 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?

CodePudding user response：

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