CODEWITHSUNDEEP
phpmysql
Joe M.
Joe M.

Reputation: 1054

Query for a list of multiple-day events occurring between today and one week from today based on start date and end date

I have a table containing a list of events with a start date and an end date. I can successfully get a list of events occurring today with the following:

SELECT * FROM event WHERE CURDATE() BETWEEN start_date and end_date

Or a list of events occurring exactly one week from today with the following:

SELECT * FROM event WHERE DATE_ADD(CURDATE() INTERVAL 1 WEEK) BETWEEN start_date and end_date

But, I want a list of events occurring anytime between today and one week from today (inclusive). Keep in mind, each event spans multiple days and is defined by a start date and end date, not a single day event.

Is there a simple way to query for this, or would it be easier to just loop through each day in PHP with the first query above (replacing CURDATE with each day from today through one week from now)?

Upvotes: 0

Views: 1083

Answers (1)

Jonathan
Jonathan

Reputation: 6732

I like to visualize problems like this. As far as I understand your question, there are four types of events that should be returned:

          |-------------------- WEEK ---------------------|
          |                                               |
          |         |========== EVENT ==========|         |
     |========== EVENT ==========|                        |
          |                        |========== EVENT ==========|
     |========================= EVENT =========================|
          |                                               |
          |-----------------------------------------------|

This means that we have:

  • events that start and end in the week;
  • events that start before the week and end in the week;
  • events that start in the week and end after the week;
  • events that start before the week and end after the week.

Translating this into SQL gives the following:

SELECT *
FROM events
WHERE (start_date >= CURDATE() AND end_date <= CURDATE() + INTERVAL 1 WEEK)
   OR (start_date < CURDATE() AND end_date >= CURDATE() )
   OR (start_date <= CURDATE() + INTERVAL 1 WEEK 
      AND end_date > CURDATE() + INTERVAL 1 WEEK)
   OR (start_date < CURDATE() AND end_date > CURDATE() + INTERVAL 1 WEEK);

I used >= and <= for the dates within the one-week period. This way we make sure that events that start or end on the start or end of the one-week period are included as well.

Assuming that start_date <= end_date is true for all rows, the simpler method is:

SELECT *
FROM events
WHERE start_date <= CURDATE() + INTERVAL 1 WEEK 
  AND CURDATE() <= end_date

Upvotes: 2

Related Questions