[RESOLVED] mysql date range of a date range

bike5 · Jun 9, 2009

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 · Jun 9, 2009

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 · Jun 9, 2009

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 · Jun 10, 2009

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

bradgrafelman · Jun 10, 2009

So add another condition?

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

Toadums · Jun 10, 2009

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 · Jun 10, 2009

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 · Jun 10, 2009

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 · Jun 10, 2009

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 · Jun 10, 2009

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 · Jun 10, 2009

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 · Jun 10, 2009

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 · Jun 10, 2009

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 · Jun 10, 2009

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 · Jun 10, 2009

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 · Jun 10, 2009

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 · Jun 10, 2009

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 · Jun 10, 2009

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 · Jun 10, 2009

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 · Jun 10, 2009

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

[RESOLVED] mysql date range of a date range

Bbike5

TToadums

BBjom

Bbike5

Bbradgrafelman

TToadums

Bbike5

Bbike5

TToadums

Bbike5

TToadums

Bbike5

TToadums

Bbike5

TToadums

Bbike5

TToadums

Bbike5

TToadums

Bbike5