[SOLVED] Trouble with BETWEEN, dates

[nvso]

I have a small calendar application and I'd need to fetch certain data from mysql with a "simple"? query. What I'm trying to achieve is:

 

Let's say I have a event "party" in database. It starts in 2008-12-28 and ends in 2009-01-03. So it lasts for a few days.. The starting date and ending date are stored to my database in those formats (y-m-d, default "date"-format)

 

Let's say I'm viewing January 2009 in my calendar. I know the date and or ( day, month, year). I'd need to fetch all data from database, which is still active in January 2009. So, I should be able to display "party" because it still is active for days 1,2,3 in January.

 

How I tried this:

 

SELECT event_id, event_date_begin, event_date_end FROM events WHERE 1 BETWEEN MONTH(k.event_date_begin) AND MONTH(event_date_end) AND 2009 BETWEEN YEAR(event_date_begin) AND YEAR(event_date_end) OR MONTH(event_date_begin) = 1 ORDER BY event_id ASC

 

The part

OR MONTH(event_date_begin) = 1 ORDER BY event_id ASC

is for events that start and end at the same day (and month). This should not be relevant to my problem..?

 

The SELECT query above works just fine without the last BETWEEN (year) statement.

 

Current outcome:

For December 2008 events show alright, query returns all necessary info. If I change to January 2009, no rows are returned

 

Any help would be highly appreciated!

[Barand]

$calstart = '2009-01-01';

$calend =  '2009-01-31'

 

SELECT * FROM events

WHERE event_date_begin <= '$calend' AND event_date_end >= '$calstart'

[nvso]

nope, doesn't work, no results

 

SELECT event_id, event_date_begin, event_date_end FROM events WHERE  event_date_begin <= 2008-12-31 AND MONTH(event_date_end) AND event_date_begin >= 2008-12-01 ORDER BY event_id ASC

 

event_date_begin and event_date_end are as "DATE" -format. This would perhaps work if the date was represented as some sort of numerical values, I suppose..

 

- Mikko

[otuatail]

$date = date('D d-M-Y' , ime(); // Get todays date

 

SELECT * FROM events WHERE event_date_begin <= $date AND event_date_end >= $date

[nvso]

Logic (in that order):

 

1. User selects month and year to generate calendar for

 

Like 1,2009 (January 2009)

 

2. SQL Queries all events where the date of event beginning or ending is somewhere in january (so even if the event is from december 28, 2008-january 1, 2009) sql could retrieve this row because it ENDS in january

 

3. PHP generates the calendar here

[otuatail]

 

 

Ok what Barand code does is only select an event if it lies totaly within the period.

 

Period = 2008/09/20 - 2008/10/20

Calender = October

 

SELECT * FROM events WHERE event_date_begin <= '$calend' OR event_date_end >= '$calstart'

 

SELECT * FROM events WHERE 2008/09/20 <= 2008/10/31 OR 2008/10/20>= 2008/10/01

 

[nvso]

Yes, but we only know the month and the year when displaying the calendar because events are queried before the calendar is generated.

 

- Mikko

[otuatail]

Surly You have a user pick the month and year. Submit this form and it takes you to a results page for the dates entered.Your problem is. working out the date end. I would sugest add a month to the start of the selected month as

 

2008/10/01 to 2008/11/01

 

Rhe query would then be SELECT * FROM events WHERE event_date_begin < '$calend' OR event_date_end >= '$calstart'

removing the = from the first part will not include the 1st day of november.

 

Desmond.

 

[mrmitch]

It may just be a simple syntax problem. Try parenthesizing (if that's a word) your and's from the or.

 

SELECT event_id, event_date_begin, event_date_end FROM events WHERE ( (1 BETWEEN MONTH(k.event_date_begin) AND MONTH(event_date_end) ) AND ( 2009 BETWEEN YEAR(event_date_begin) AND YEAR(event_date_end) ) ) OR MONTH(event_date_begin) = 1 ORDER BY event_id ASC

[mrmitch]

Scratch that - Use DATEDIFF on Jan 1 2009 and Jan 31 2009 checking for >=0 and <=0 instead of the 2 betweens.

 

DATEDIFF( event_date_end, '01-01-2008')>=0 (ends after jan 1) and DATEDIFF(event_date_begin,'01-31-2008')<=0 (begins before jan 31)

 

This will find any date that begins before the end of the month AND ends after the beginning of the month. So beginning in November 2008 and ending in Jan 28 2009 (or Feb) matches because it begins before the end of January and ends after the beginning of January. Jan 2 - Jan5 begins before the end of the month and ends after the beginning of the month. This removes the need for the OR in your select too.

 

Hope this helps!

 

Mitch

3CG Developing (ask.3cgdeveloping.com)

 

 

The between on your year is filtering everything out. It's looking for events that will start in 2008 and end in 2010 to find 2009 between.

 

 

 

[nvso]

Ok thanks, getting closer to solving this topic..

 

Now though, this query returns no rows although there is at least one line where event begins in december 2008 and ends in january 2009:

 

SELECT event_id, event_date_begin, event_date_end FROM events WHERE DATEDIFF( event_date_end, 2008-12-01)>=0 AND DATEDIFF(event_date_begin, 2008-12-31)<=0

 

2008-12-01 and 2008-12-31 are php generated, first day of the month and last day of the month

 

Is my query constructed somehow wrong, no errors are thrown anyways?

 

- M

[mrmitch]

 

The lower date needs to go first to produce a positive result. I put > on both - sorry.

 

begin date, beginning before '2008-12-31' is correct with the event_begn first, because it will be the earlier date.

 

End date needs reversed, or the sign changed. Ending after '2008-12-01', so Dec 1st is always the earlier date.

 

DATEDIFF('2008-12-01',event_date_end)>=0

 

Hope this helps!

 

Mitch

 

 

 

 

[mrmitch]

Wrote that backwards too - greater date goes first, so just reverse my logic in the previous post.

 

DATEDIFF('2008-12-31',event_date_begin)>=0

[Barand]

 

Ok what Barand code does is only select an event if it lies totaly within the period.

 

 

My code selects events A, B, C, D below but not E, F

[pre]

      Events                  calstart            calend

                                  |                  |

        A                S----------------E          |

                                  |                  |

        B                        |        S--------------E

                                  |                  |

        C                        |  S----------E    |

                                  |                  |

        D          S---------------------------------------E

                                  |                  |

        E            S-----E    |                  |

                                  |                  |

        F                        |                  |  S----------E

[/pre]

[Barand]

nope, doesn't work, no results

 

SELECT event_id, event_date_begin, event_date_end FROM events WHERE  event_date_begin <= 2008-12-31 AND MONTH(event_date_end) AND event_date_begin >= 2008-12-01 ORDER BY event_id ASC

 

event_date_begin and event_date_end are as "DATE" -format. This would perhaps work if the date was represented as some sort of numerical values, I suppose..

 

- Mikko

 

You need to put date strings in quotes '2008-12-31', otherwise it's intepreted as 2008 minus 12 minus 31

[nvso]

Yes, that would show me events occurring in selected month / year, say 12/2008. Those are displayed ok, BUT if the event continues to January 2009 Barand's query stops working - at least for me. See, to remind you that only ONE month is displayed at once. So, when I change to January the query changes to:

 

event_date_begin <= 2009-01-01 AND event_date_begin >= 2009-01-31

 

Now that the event that starts in december and ends in january will not be found because this query assumes that the beginning and ending of this event is somewhere in January.

 

- M

[Barand]

your code above is not mine. Check column names and constants carefully. This is mine

 

The data:

 

[pre]

mysql> SELECT * FROM `datetest`

    -> ORDER BY `event`;

+----+------------------+-------+----------------+

| id | event_start_date | event | event_end_date |

+----+------------------+-------+----------------+

|  3 | 2008-08-29      | A    | 2008-09-03    |

|  2 | 2008-09-07      | B    | 2008-11-01    |

|  1 | 2008-09-01      | C    | 2008-09-10    |

|  5 | 2008-08-01      | D    | 2008-11-12    |

|  6 | 2008-08-20      | E    | 2008-08-29    |

|  4 | 2008-10-08      | F    | 2008-10-10    |

+----+------------------+-------+----------------+

6 rows in set (0.00 sec)

[/pre]

 

The query:

mysql> SELECT * FROM `datetest`
    -> WHERE event_start_date <= '2008-09-30'
    -> AND event_end_date >= '2008-09-01'
    -> ORDER BY `event`;
+----+------------------+-------+----------------+
| id | event_start_date | event | event_end_date |
+----+------------------+-------+----------------+
|  3 | 2008-08-29       | A     | 2008-09-03     |
|  2 | 2008-09-07       | B     | 2008-11-01     |
|  1 | 2008-09-01       | C     | 2008-09-10     |
|  5 | 2008-08-01       | D     | 2008-11-12     |
+----+------------------+-------+----------------+
4 rows in set (0.00 sec)

 

The logic is simple. Include events WHERE

 

event starts before calendar ends

AND

event ends after calendar starts

[nvso]

Sure, it works - as long as year does not change..? Or at least that's how it seems. Should I try to convert the field values to seconds or something? Is that possible?

 

- M

[Barand]

OK, for the last time, here it is with year changes

+----+------------------+-------+----------------+
| id | event_start_date | event | event_end_date |
+----+------------------+-------+----------------+
|  1 | 2008-01-01       | C     | 2008-01-10     |
|  2 | 2008-01-07       | B     | 2008-03-01     |
|  3 | 2007-12-29       | A     | 2008-01-03     |
|  4 | 2008-02-08       | F     | 2008-02-10     |
|  5 | 2007-12-01       | D     | 2008-03-12     |
|  6 | 2007-12-20       | E     | 2007-12-29     |
+----+------------------+-------+----------------+

query
[code]mysql> SELECT * FROM `datetest`
    -> WHERE event_start_date < '2008-01-30'
    -> AND event_end_date > '2008-01-01'
    -> ORDER BY `event`;
+----+------------------+-------+----------------+
| id | event_start_date | event | event_end_date |
+----+------------------+-------+----------------+
|  3 | 2007-12-29       | A     | 2008-01-03     |
|  2 | 2008-01-07       | B     | 2008-03-01     |
|  1 | 2008-01-01       | C     | 2008-01-10     |
|  5 | 2007-12-01       | D     | 2008-03-12     |
+----+------------------+-------+----------------+

 

Now I've given you a working query. It's up to you to use it correctly with correctly formatted dates. Good luck.

[nvso]

Ok. That works. Thank you so very much