php mysql日期区间,php 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).

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

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]

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.

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