10

Possible Duplicate:
Get from database but only last 30 days

Hi I have some php code which I use to count the rows in a database from the last 30 days. The problem is, that if I change the piece of code so that the number changes from -30 to -20, the output number goes from 272 to 360 instead of going down.

Here is the code:

$result = mysql_query("SELECT * FROM all_count WHERE DATEDIFF(date,NOW()) = -30 and member ='000002'");
$num_rows60 = mysql_num_rows($result);
Community
  • 1
  • 1
Sam Williams
  • 175
  • 2
  • 3
  • 12
  • Same question in full description http://stackoverflow.com/questions/13912035/display-record-older-than-3-months-in-sql/13912197#13912197 – softsdev Dec 21 '12 at 21:24
  • You are selecting records that are *exactly* 30 or 20 days old. – JJJ Dec 22 '12 at 20:54

4 Answers4

17

Try this

select * from `table` where `yourfield` >= DATE_SUB(CURDATE(), INTERVAL 3 MONTH)

For days, year see below for example.

DATE_SUB(CURDATE(), INTERVAL 15 DAY) /*For getting record specific days*/

DATE_SUB(CURDATE(), INTERVAL 1 YEAR) /*for getting records specific years*/

For Anand, query 
BETWEEN DATE_SUB( CURDATE( ) ,INTERVAL 6 MONTH ) AND DATE_SUB( CURDATE() ,INTERVAL 3 MONTH ) 
/* For Getting records between last 6 month to last 3 month
softsdev
  • 1,478
  • 2
  • 12
  • 27
3

It's better to compare

`date`< DATE(NOW() - INTERVAL 30 DAY)

rather than

 DATEDIFF(date,NOW()) = -30

In the first case, the date calculation is done only once, at the beginning of the query, and the database can use any indexes on the date column.

The second query must calculate the DATEDIFF on every row, and the database can't use any indexes. The second query forces a full table scan.

Also, I strongly suggest that you not call your column date. Yes, I know you can quote the name with backticks, but that's just messy and when you forget then your syntax errors will be hard to forget. Come up with a more descriptive name for the column. What kind of date is it? What does the date represent?

Andy Lester
  • 91,102
  • 13
  • 100
  • 152
2

You can use this instead:

$result = mysql_query("SELECT * FROM all_count WHERE `date`< DATE(NOW() - INTERVAL 30 DAY) and     member ='000002'");
aynber
  • 22,380
  • 8
  • 50
  • 63
0

As you can see in the documentation here, the DATEDIFF function in MySQL tells you the difference in days of the first date to the second.

Your query only selects all rows where the difference is exactly 30 days and not those that are up to 30 days ago. So it's completely possible, that the number of rows for the date 20 days ago is higher than 30 days ago. What you most likely wanted was:

SELECT * FROM all_count WHERE DATEDIFF(date,NOW()) >= -30 and member ='000002'
s1lence
  • 2,188
  • 2
  • 16
  • 34