[RESOLVED] mysql date range of a date range

[bike5]

Yes each row has a start and end date. I am viewing a calendar of 1 whole month. I want to grab all rows that have events that fall within that month. So an event can start before the month you are looking at and span into it. It can span the entire month and actually start and end outside the month. It could start and stop in the month or start in the month and span into the next month.

[Toadums]

Oh. OK!!

So you just want all events that at anytime are in the month you are looking at.

Hm...

I guess first you have to get the first/last days of a month (formatted something like 2009-05-05). i am guessing you have a means to do that.

Took me a minute...but I think i covered every scenario. Thanks to Discrete Mathematics teaching me logic I think I have put something together.

Start_Date and End_Date are the fields from your SQL database.

$FirstDay and $LastDay are the first and last days of the month (like i said, hopefully you have a means of getting these)

$query="SELECT * FROM `pdem_timesheet`.`tblTimesheet`
WHERE ((Start_Date >= '$FirstDay') OR (End_Date <= '$LastDay') OR ((Start_Date <= '$FirstDay') AND (End_Date >= '$LastDay')))
ORDER BY `tblTimesheet`.`Date` DESC LIMIT $startRow , $numRows  "; 

Hopefully you can use brackets in an sql query..otherwise the logic wont work 😛

Paul

[bike5]

WHERE 
(
	(Start_Date > $FirstDay) OR 
	(End_Date < $LastDay) OR 
	(
		(Start_Date < $FirstDay) AND
		(End_Date > $LastDay)
	)
)

Without being able to run this right now I am pretty sure that will return all results. If you grab everything with start dates greater then the first day it is going to grab all events that start in the future even beyond the month you are looking at. It will do the same but reversed on your end date being lower then last day, it will grab all events in the past even if they are over in the past months, therefore you just grabbed all results without any conditions essentially.

[Toadums]

oh fine so there was a bug lol 😛 it took me a while to get good at logic anyways lol.

$query="SELECT * FROM `pdem_timesheet`.`tblTimesheet`
WHERE (((Start_Date >= '$FirstDay') AND (Start_Date <= 'LastDay')) OR 
((End_Date <= '$LastDay') AND (End_Date >='$FirstDay')) OR 
((Start_Date <= '$FirstDay') AND (End_Date >= '$LastDay')))
ORDER BY `tblTimesheet`.`Date` DESC LIMIT $startRow , $numRows  "; 

There is probably a syntax error/misused bracket or 2 in there 😛 just so you know

pseudocode:

if the first day of an event is greater than the first day of a month AND less than the last day. (if it is in the month)

or if the last day of an event is less than the last day AND greater than the first day of a month (it is in the month)

if an event starts before the month in question AND ends in a month AFTER the month in question. the even includes atleast the entire month

there! lol

[bike5]

WHERE
(
	(
		(Start_Date >= '$FirstDay') AND 
		(Start_Date <= 'LastDay')
	) OR
	(
		(End_Date <= '$LastDay') AND 
		(End_Date >='$FirstDay')
	) OR
	(
		(Start_Date <= '$FirstDay') AND 
		(End_Date >= '$LastDay')
	)
)

Sorry to say this but it still has holes but it is getting closer..lol

You have getting rows that start in the current month and end in the current month and span over the entire month. We still are missing days that start in a previous month and end in current month and days that start in current month and end in a future month.

Oh btw: thanks for the help in the meantime 😉

[Toadums]

bike5;10917942 wrote:

WHERE
(
	(
		(Start_Date >= '$FirstDay') AND 
		(Start_Date <= 'LastDay') 
	) OR
	(
		(End_Date <= '$LastDay') AND 
		(End_Date >='$FirstDay')
	) OR
	(
		(Start_Date <= '$FirstDay') AND 
		(End_Date >= '$LastDay')
	)
)

Sorry to say this but it still has holes but it is getting closer..lol

You have getting rows that start in the current month and end in the current month and span over the entire month. We still are missing days that start in a previous month and end in current month and days that start in current month and end in a future month.

Uhm, lol...read what you said really carefully 😛

[bike5]

You have getting rows that start in the current month and end in the current month and span over the entire month. We still are missing days that start in a previous month and end in current month and days that start in current month and end in a future month.

How about this:

You have getting rows that "(start in the current month and end in the current month)" or that "(start in a previous month and end in a future month)" We still are missing days that start in a previous month and end in current month and days that start in current month and end in a future month.

wait wait..lol I am re-reading this again to double check..hehe

[Toadums]

bike5;10917945 wrote:

How about this:

You have getting rows that "(start in the current month OR end in the current month)" or that "(start in a previous month and end in a future month)" We still are missing days that start in a previous month and end in current month and days that start in current month and end in a future month.

Lol...i must be missin something. cause to me it looks like that ^

...lol

[bike5]

Ok you may be right let me loo k again, I wish I had the database to test but its at work.. 1 sec 😉

[Toadums]

bike5;10917942 wrote:

WHERE
(
	[COLOR="SeaGreen"](
		(Start_Date >= '$FirstDay') AND 
		(Start_Date <= 'LastDay')
	) [/COLOR][B]OR[/B]
	[COLOR="Red"](
		(End_Date <= '$LastDay') AND
		(End_Date >='$FirstDay')
	) [/COLOR][B]OR[/B]
	[COLOR="RoyalBlue"](
		(Start_Date <= '$FirstDay') AND
		(End_Date >= '$LastDay')
	)[/COLOR]
)

maybe that highlighting will make it easier. The first 2 are seperated by an or not an and

if the start of the event falls in the month
OR
if the end of the event falls in the month
OR
if it starts in a previous month AND finishes in a later month

then run the query

[bike5]

Hey after going over it a little more closely I think you are right, my bad. Thanks for the help Toadums.

[Toadums]

No problemo!! Yay this is the first time i helped someone on the forum D: