MySQL dates between start and end dates of event

Question

I am working on PHP and MySQL based application. You can create events in it, make reservations and so on. Let's assume I have MySql table:

--table 'events'
+------+---------------------+---------------------+
| name |      startdate      |       enddate       |
+------+---------------------+---------------------+
| aaa  | 2014-10-28 00:00:00 | 2014-10-28 23:59:59 |
| bbb  | 2015-01-01 10:00:00 | 2015-01-01 16:00:00 |
| ccc  | 2014-12-24 17:00:00 | 2014-12-28 08:30:30 |
+------+---------------------+---------------------+

What I need is to get all dates, where the events take place. So, for events which start and end on the same day, I would like to get the single date, and for multiple day events, I would like to get the start date and then dates through the end date. So, the expected result would look like this:

+------------+
|   dates    |
+------------+
| 2014-10-28 |
| 2015-01-01 |
| 2014-12-24 |
| 2014-12-25 |
| 2014-12-26 |
| 2014-12-27 |
| 2014-12-28 |
+------------+

Of course, I have tried to search here and know, that there are similar questions answered, but they are just showing dates betwen given date, and do not take in mind 'one day' events.

Thanks in advance for any help.

EDIT: The reason to get this is to show on which days have any events in calendar view.

you can write logic and make this op. don't do too much stuff in mysql query. It affect performance — ashokhein, Dec 04 '14 at 08:53
I do not think it is necessary. I have now added the reason why I need this. — Richard Šimek, Dec 04 '14 at 09:05

score 1 · Accepted Answer · answered Dec 04 '14 at 09:08

1

$getEvents = mysqli_query("SELECT ...... You query");
$row_getEvents = mysqli_fetch_assoc($getEvents);

do {
    $date = date("Y-m-d", strtotime($row_getEvents['startdate']));
    $end_date = date("Y-m-d", strtotime($row_getEvents['enddate']));

    while (strtotime($date) <= strtotime($end_date)) {
        echo "$date\n";
        $date = date ("Y-m-d", strtotime("+1 day", strtotime($date)));
    }
} while ($row_getEvents = mysqli_fetch_assoc($getEvents));

answered Dec 04 '14 at 09:08

Ghassan Elias

2,213
1
14
17

I would prefer pure MySQL solution, but I will give this a try. – Richard Šimek Dec 04 '14 at 09:12
Your tags include PHP – Ghassan Elias Dec 04 '14 at 09:13
Worked like charm! Many thanks! Still, I would like to see if anyone suggets pure MySQL solution, because I think it would be faster for MySQL to achieve this, when we consider processing time. – Richard Šimek Dec 04 '14 at 09:19
try this http://stackoverflow.com/questions/2157282/generate-days-from-date-range – Ghassan Elias Dec 04 '14 at 09:41
1

Your solution with PHP is shorer and simplier. And from what I have found, it's not slower than MySQL in this case, when we have +- 40 rows in one query – Richard Šimek Dec 04 '14 at 10:59

score -1 · Answer 2 · answered Dec 04 '14 at 08:54

-1

SELECT name 
FROM events 
WHERE start BETWEEN ' 2014-10-28' AND '2014-10-28'

try this

answered Dec 04 '14 at 08:54

Ferrakkem Bhuiyan

2,741
2
22
38

what the reason of down-vote ..would you kindly explain it ? – Ferrakkem Bhuiyan Dec 04 '14 at 08:57
this just selects the events which start in the given date range. so, not working for me – Richard Šimek Dec 04 '14 at 08:59

MySQL dates between start and end dates of event

2 Answers2