根据开始日期和结束日期查询今天和从今天起一周之间发生的多日事件列表 [英] 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.

有没有一种简单的方法来查询这个，或者用上面的第一个查询在 PHP 中循环每一天会更容易吗(从今天到一周后的每一天都用 CURDATE 替换)?

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

推荐答案

我喜欢把问题想象成这样.据我了解您的问题，应该返回四种类型的事件:

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

这意味着我们有:

• 在一周内开始和结束的事件；
• 在一周之前开始并在一周内结束的事件；
• 在一周内开始并在一周后结束的事件；
• 在一周之前开始并在一周之后结束的事件.

将其转换为 SQL 给出以下内容:

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.

假设 start_date <= end_date 对所有行都为真，更简单的方法是:

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 

这篇关于根据开始日期和结束日期查询今天和从今天起一周之间发生的多日事件列表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！