php mysql双日期范围 [英] php mysql double date range

问题描述

我正在处理一个事件callendar，并且无法找到在给定范围内选择我的事件的mysql查询。我的活动有开始/结束日期，所以我的范围（月）。

I am working on an event callendar and am having trouble finding a mysql query that selects my events within a given range. My events have start/end dates and so do my ranges (Months).

我已尽力描绘我正在寻找的内容：（我想选择事件1,2,3,4但不是5或6）

I have tried my best to depict what I am looking for : (I want to select events 1,2,3,4 but not 5 or 6)

         |========= April ===========|            => Range
     +--(1)--+                   +--(2)--+        => Partialy Overlapping Events
                   +--(3)--+                      => Events included in range
     +----------------(4)----------------+        => Ovelapping events
+-(5)-+                                 +-(6)-+   => Events outside of range

我发现这个相似的标准： 2列Mysql日期范围在PHP中搜索，但我不认为这是一个重复的，就好像我明白了我的问题范围有开始和结束日期，另一个问题是范围是单个日期。

I have found this similar quastion : 2 Column Mysql Date Range Search in PHP but I dont think this is a duplicate as if I understand correcly in my problem the range has start and end dates and in the other question the range is a single date.

推荐答案

解决方案仍然是非常类似于您链接的问题;尝试这个查询：

The solution is still very similar to the question you're linking to; try this query:

SELECT * FROM events e
    WHERE `start` <= [RANGE.end]
    AND `end`  >= [RANGE.start]

你当然有在您的范围的第一个和最后一个日期替换[RANGE.start]和[RANGE.end]。如果例如RANGE.start ='2011-04-01'和RANGE.end ='2011-04-30'，上述查询将给出所有在11月4日发生的结果。

You'd of course have to replace [RANGE.start] and [RANGE.end] by the first and last date of your range. If e.g. RANGE.start = '2011-04-01' and RANGE.end = '2011-04-30', the above query will give all results which are happening in April '11.

根据您是否要选择仅触摸范围的事件（意味着它们具有共同的边界日期，但实际上不重叠），您可以替换< = / > = by / > 。

Depending on whether you want to select events which just "touch" the range (meaning they have a common border date, but do not actually overlap) or not, you can replace <=/>= by </>.

这篇关于php mysql双日期范围的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！