[SOLVED] Selecting records between two dates

[86Stang]

I'm trying to get all records between 72 hours in the past until now.  The field I have to work with, join_date, is an int(10) field with 10 digits in the field (unix timestamp maybe?).  I've tried this but it doesn't work but not throwing an error:

 

SELECT COUNT(*) FROM `table` WHERE join_date < NOW() - INTERVAL 3 DAYS

 

Any clue?

[haku]

That won't work, as mysql dates aren't unix timestamps. You will first have to get the date in php, then pass that to your query:

 

$time = time() - (60*60*72);

$query = 'SELECT COUNT(id) FROM whatever WHERE time < ' . $time;

 

As a side note, using COUNT(*) is quite inefficient. You should put the name of a column name that always has a value instead (such as id).

[Ken2k7]

Not tested. Try this -

SELECT COUNT(*) FROM table WHERE join_date >= DATE_SUB(NOW(), INTERVAL 3 DAY);

[haku]

I don't think that will work either, as it appears he is storing the date as a unix timestamp.

[86Stang]

haku, your solution was dead on.  Thanks!