limit results to a date range?

[jameslat]

Hi all, How can I limit my search results to a certain date range?

I have a counter on my site that inserts a new row into my table "counter" every time someone loads my homepage.

How can I Limit the results to the current year, or the current month in this format 2011-07-09 09:33:12 ?

 

 

Thanks for any help!

-James

[PFMaBiSmAd]

You would extract the portion of your stored dates that you want to match (there are a bunch of mysql date/time functions to do this) and compare them either with specific numbers (2011 for this year or 7 for this month) or you can dynamically extract the current year and/or current month -

 

SELECT * FROM counter WHERE YEAR(your_date_time_column) = 2010; -- match all the rows from last year

 

SELECT * FROM counter WHERE YEAR(your_date_time_column) = YEAR(CURDATE()); -- match all the rows from the current year

 

SELECT * FROM counter WHERE MONTH(your_date_time_column) = 6; -- match all the rows from the month of June (all years)

 

SELECT * FROM counter WHERE MONTH(your_date_time_column) = MONTH(CURDATE()); -- match all the rows from the current month (all years)

 

SELECT * FROM counter WHERE EXTRACT(YEAR_MONTH, your_date_time_column) = EXTRACT(YEAR_MONTH, CURDATE()); -- match all the rows from the current year and current month

 

SELECT * FROM counter WHERE EXTRACT(YEAR_MONTH, your_date_time_column) = 201012; -- match all the rows from 2010 December (month 12)

 

You can also use a BETWEEN min AND max comparisons to match dates over any range of dates you want (multi years, multi months...)

[jameslat]

Thanks a lot for helping to clear that up with me, but my code still isn't working for me?

I'm trying to find out how many results there are and output them to the use to say something like "X amount of results this month".

What is wrong with my code?

$query = "SELECT * FROM counter WHERE EXTRACT(YEAR_MONTH, 'timestamp') = EXTRACT(YEAR_MONTH, CURDATE())";

$result = mysql_query($query);

$numresults=mysql_query($query);

$numrows=mysql_num_rows($numresults);

 

timestamp is the name of the column holding my timestamp.

[PFMaBiSmAd]

'timestamp' is a string consisting of the letters t, i, m, e, s, t, a, m,  and p. timestamp (with no quotes around it) is your column name.

[jameslat]

alright i made that adjustment but it's still giving me an error msg of

"mysql_num_rows(): supplied argument is not a valid MySQL"

could it be my code is right but it's not being counter correctly? or maybe is having "timestamp" as a column name messing it up?

 

(btw thnx for helping me thus far)

[jameslat]

Hey I just figured it out and i wanted to post my code to improve the community so here it is

 

$query = "SELECT * FROM counter WHERE extract(year_month FROM timestamp) = extract(year_month FROM CURDATE())";

$result = mysql_query($query);

$numresults=mysql_query($query);

$numrows=mysql_num_rows($numresults);

 

echo "You have had a total of ".$numrows." visits in the last 30 days.";

echo "<br />";

 

thanks for all the help!

-James