php mysql double date range

Question

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.

How is your range given, what is your table schema for the events table? — codeling
– codeling, Commented Dec 19, 2011 at 10:05

codeling · Accepted Answer · 2011-12-19 10:33:16Z

5

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

edited Dec 19, 2011 at 10:33

answered Dec 19, 2011 at 10:10

codeling

11.5k6 gold badges48 silver badges76 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Purplefish32 Over a year ago

I see now, makes perfect sense

Tomasz Majerski Over a year ago

I was trying to make VERY LARGE query for this and You saved me alot of time, thanks.

Tom Mac · Accepted Answer · 2011-12-19 10:16:40Z

Give this a go. I made up a table called dateRangeExample to illustrate the answer:

drop table if exists dateRangeExample;

create table dateRangeExample
(id int unsigned primary key,
startDate date not null,
endDate date not null
);


insert into dateRangeExample (id,startDate,endDate) values (1,'2011-03-15','2011-04-05');
insert into dateRangeExample (id,startDate,endDate) values (2,'2011-04-25','2011-05-05');
insert into dateRangeExample (id,startDate,endDate) values (3,'2011-04-10','2011-04-15');
insert into dateRangeExample (id,startDate,endDate) values (4,'2011-03-15','2011-05-05');
insert into dateRangeExample (id,startDate,endDate) values (5,'2011-03-01','2011-03-20');
insert into dateRangeExample (id,startDate,endDate) values (6,'2011-05-03','2011-05-25');

select dre.*
from dateRangeExample dre
where startDate between '2011-04-01' and '2011-04-30'
or endDate between '2011-04-01' and '2011-04-30'
or (startDate < '2011-04-01' and endDate > '2011-04-30');

Collectives™ on Stack Overflow

php mysql double date range

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related