[RESOLVED] mysql date range of a date range

[bike5]

How would you go about writing a query to select results based on a range of dates. This is for a calendar, I want to grab all results within a given month. The tricky part is, the start date could be in the previous month and ending in current month, or ending in a future month. Start date in current month end in current month, or end in future month. So I have a date range for the month say 1/1/09 - 1/31/09 (can format date in any way). The articles have 2 datetime columns, start_date and end_date. Ideas?

Thank you

[Toadums]

I came on here to ask pretty much the same question. Maybe we can help eachother.

What I have done so far (doesnt work) is pass in a date variable, formatted yyyy-mm-dd. (for now it is just a string, but it WOULD be passed in [as a string])

Then right before my query i have this code:

$Date="2009-05-01"

	$year = substr($Date,0,4);
	$month = substr($Date,5,2);
	$day =substr($Date,8,2);

$dateMK = mktime(0,0,0,date($month),date($day)+7,date($year));	

while($date < $dateMK){
do query

$day = date($day)+1;

$dateMK = mktime(0,0,0,date($month),date($day),date($year));	
}

My problem is just that I dont know how to grab the info from the query. I will wait to see if someone answers your question, then see if I can use that. else just start my own topic 🙂

Maybe you can figure out how to fix your problem using my code. (ps. I am not sure if the way I incremented it at the bottom of the while loop is correct)

Hope this helps!
Paul

[Bjom]

SELECT myField FROM myTable WHERE myTime BETWEEN '2009-03-27 10:43:00' AND '2009-04-27 10:43:00';

This should do the job?

Bjom

[bike5]

Bjom, that only works for selecting 1 date in between two dates. This is selecting two dates in between two dates.

[bradgrafelman]

So add another condition?

WHERE (date_start BETWEEN 'date1' AND 'date2' OR date_end BETWEEN 'date1' AND 'date2')

[Toadums]

Wow. that is actually really freakin easy!!! lol

Bike, here is what I did, works perfectly, you could just obviously replace the strings (just there for testing) with '$Date' variables or what ever you want. But here it is.

 $query="SELECT * FROM `pdem_timesheet`.`tblTimesheet`
WHERE Name= '$queryName' AND Date >= '2009-05-22' AND Date <= '2009-06-22'
ORDER BY `tblTimesheet`.`Date` DESC LIMIT $startRow , $numRows  "; 

Hope this helps!

[bike5]

So add another condition?

Code:

WHERE (date_start BETWEEN 'date1' AND 'date2' OR date_end BETWEEN 'date1' AND 'date2')

Maybe I am wrong but if the start date (of an item in the database) was 5/5/09 and the end date was 7/5/09 and I was searching inside the 6th month (6/1/09-6/30/09) that query would not return the item because the start and end dates did not fall within the month itself.

[bike5]

Toadums;10917926 wrote:

Wow. that is actually really freakin easy!!! lol

Bike, here is what I did, works perfectly, you could just obviously replace the strings (just there for testing) with '$Date' variables or what ever you want. But here it is.

 $query="SELECT * FROM `pdem_timesheet`.`tblTimesheet`
WHERE Name= '$queryName' AND Date >= '2009-05-22' AND Date <= '2009-06-22'
ORDER BY `tblTimesheet`.`Date` DESC LIMIT $startRow , $numRows  "; 

Hope this helps!

Your query only seems to be searching one 1 field "date", where does the range come in (start-end date)? I am working with 4 dates, 2 start and 2 end dates, you seem to be working with 3, unless I am mistaken?

[Toadums]

Wait. So you mean you have 2 fields in your sql database? one field is the start, one is the end? what do you mean by

The articles have 2 datetime columns, start_date and end_date. Ideas?

?

or you have 2 textboxes on a form that someone enters dates into? or what..? Where do these start and end dates come from, and what fields in your sql database have to do with date

[bike5]

I have a article (event) in the database that has a starting and ending date. Someone could have an event that spans multiple months, day after day consecutively.

[Toadums]

ok...maybe im just tired..but im still not gettin it lol..

So you have your sql database.

inside it are tables.

there is a table called say Calendar.

In table calendar there are records (rows).

each record has a field "start date" and "end date".

you would like to run a query to do what exactly? find all events between 2 dates (2 dates comming from other events...?)

sorry for not getting this 😛

[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 😉