In MySQL grab items within start_date and end_date

Question

I have events listed in my MySQL database.

Events have a start_date (date) and end_date (date) fields.

In my application users can click on a specific day using a calendar. So, if they click today they are taken to /events?day=2013-08-08 and all events from that day are shown on the page.

This was working great until I started adding multi day events. Since previously I was just asking MySQL for all events where start_date == the user chose date, I only get events that START on that day, BUT I also want events that may have started before that day but are still going ON that day.

How can I query items like this? All events that either start on this day, or started earlier but havent yet ended by this day.

As long as the type of field is DATE, you can compare them using < > and DATE_SUB http://stackoverflow.com/questions/13448340/sql-greater-than-date — user1020317, Aug 08 '13 at 15:23

score 8 · Accepted Answer · answered Aug 08 '13 at 15:23

8

SELECT ...
FROM yourtable
WHERE '2013-08-08' BETWEEN start_end AND end_date

where

foo BETWEEN bar AND baz

is the SQL equivalent of

(bar <= foo) AND (foo <= baz)

answered Aug 08 '13 at 15:23

Marc B

356,200
43
426
500

I believe `OR ('2013-08-08' = start_date OR '2013-08-08' = end_date)` should be added as well as you ask for `BETWEEN` while 'on' is also valid – AmazingDreams Aug 08 '13 at 15:25
1

no, totally unecessary. `between` is inclusive, e.g. `<=`, not `<`. – Marc B Aug 08 '13 at 15:27
You are right: http://dev.mysql.com/doc/refman/5.5/en/comparison-operators.html#operator_between – AmazingDreams Aug 08 '13 at 15:33

score 0 · Answer 2 · answered Aug 08 '13 at 15:24

0

where start_date>='$inputdate' AND end_date<='$inputdate'

Should do it

answered Aug 08 '13 at 15:24

Dave

3,280
2
22
40

score 0 · Answer 3 · answered Aug 08 '13 at 15:27

0

If your end date is not yet set for the tasks still going on:

where '2013-08-08' == start_date or end_date is null

answered Aug 08 '13 at 15:27

Travis Hegner

2,465
1
12
11

In MySQL grab items within start_date and end_date

3 Answers3