CODEWITHSUNDEEP
phpmysql
Cream Whipped Airplane
Cream Whipped Airplane

Reputation: 1367

Select events that take place in given month

I am trying to select events from my database that are taking place in a given month. I can't seem to figure out how to select those events that started before the given month and end after it.

I know I should probably get the date range between the two dates and see if the given month is inside this range. I would also like to create a column range which has three values based on the range of the event. When the event starts and ends inside the given month this value will be 'fits'. When the event starts in the given month, but extends past it the value is 'extends'. When it starts before the given month, but end in it, it's 'past'. And finally when it started before and goes further the value is 'full'

What I don't know is how to approach the implementation of this. Can all this be done with MySQL, or does the second part need to be implemented in PHP?

Here's an example of the table structure:

| Event_id | Date_from           | Date_to             |
+----------+----------------------+--------------------+
|    1     | 2014-03-05 19:28:00 | 2014-03-06 09:00:00 |
|    2     | 2014-02-05 00:00:00 | 2014-03-07 10:00:00 |
|    3     | 2014-03-28 15:00:00 | 2014-05-06 00:00:00 |
|    4     | 2014-02-05 00:00:00 | 2014-05-06 00:00:00 |
|    5     | 2014-05-06 19:28:00 | 2014-06-03 19:28:00 |

And the result of the select statement I am trying to achieve should look like this:

| Event_id | Date_from           | Date_to             | Range     |
+----------+----------------------+----------------------+---------+
|    1     | 2014-03-05 19:28:00 | 2014-03-06 09:00:00 | 'fits'    |
|    2     | 2014-02-05 00:00:00 | 2014-03-07 10:00:00 | 'past'    |
|    3     | 2014-03-28 15:00:00 | 2014-05-06 00:00:00 | 'extends' |
|    4     | 2014-02-05 00:00:00 | 2014-05-06 00:00:00 | 'full'    |

I am completely clueless at this point, therefore I will really appreciate any advice and tips on how this could be done. Thank you very much for reading this and for your help.

PS: Before I realized I need to take in account the events that match the 'full' range criteria I had this:

SELECT * FROM `eventstesting` 
WHERE   `e_to` < '2014-04-01' OR
        `e_from` > '2014-03-01'

Which successfully selects the events that match the 'past', 'fits' and 'extends' criteria, but still doesn't generate the range column.

I have also checked this topic MySQL - Get a list of months on which events are taking place, but it didn't help me

Upvotes: 2

Views: 79

Answers (2)

Strawberry
Strawberry

Reputation: 33945

?

SELECT *
     , CASE WHEN date_from >= '2014-03-01' AND date_to < '2014-04-01' THEN 'fits' 
            WHEN date_from < '2014-03-01' AND date_to BETWEEN '2014-03-01' AND '2014-04-01' THEN 'past' 
            WHEN date_from BETWEEN '2014-03-01' AND '2014-04-01' AND date_to >= '2014-04-01' THEN 'extends' 
            WHEN date_from <= '2014-03-01' AND date_to >= '2014-04-01' THEN 'full' 
        END `range` 
  FROM eventstesting
 HAVING `range` IS NOT NULL;

Upvotes: 2

Steve Horvath
Steve Horvath

Reputation: 531

date range selection trick - I think this is what you're looking for:

start < month_end_date
and
end > month_start_date

Basically you select any event that starts before the month ends and ends after the months' start - i.e. it's either partially or fully overlapping with the month.

I'm not sure if this is exactly what you need, but this is how you usually compare date ranges efficiently.

Ah, there's one more thing: if dates can be 'open' (i.e. an event can still be on-going, and the end date is still NULL, that has to be taken care of separately)

Upvotes: 0

Related Questions