CODEWITHSUNDEEP
phpmysqldate
user2320500
user2320500

Reputation: 169

SELECT all rows that are 2 WEEKS old FROM NOW

I have a query that selects all rows of data from a table in my database. I am using PHP and MySQL. Instead of grabbing all of the rows in my table, I would like to only grab rows that are between now and two weeks old. How can I do this? I know I would need to add a WHERE clause

My Code So far:

$notifications_query= "SELECT * FROM notifications ORDER BY recieved DESC";

The column containing the date that will be used is named 'viewed' and it is in 'datetime' format.

Thanks!

Upvotes: 2

Views: 995

Answers (6)

James Green
James Green

Reputation: 1753

You've had an assortment of answers, because there are a whole bunch of ways to do this -- and some subtleties.

The simplest, if your received value is NEVER in the future, is to do something like...

WHERE received > DATE_SUB( CURDATE(), INTERVAL 14 DAY )

or 

WHERE DATEDIFF( CURDATE(), received ) < 14

If your received value can be in the future as well, and you just want the slice from 14 days ago to today, that's not much trickier:

WHERE received BETWEEN DATE_SUB( CURDATE(), INTERVAL 14 DAY ) AND CURDATE()

or again, 

WHERE DATEDIFF( CURDATE(), received ) BETWEEN 0 AND 14

One thing you should think about in addition to this: is received a date field, or a datetime field? CURDATE() is always midnight of today when compared to a datetime, so these BETWEEN queries may actually omit datetimes that fall during the course of today -- as they will fall after (e.g.) 2013-06-19 00:00:00, the datetime value of CURDATE. If that's an issue for you, use NOW() instead.

Upvotes: 0

Fabio
Fabio

Reputation: 23510

Try this

$notifications_query= "SELECT * FROM notifications WHERE yourdatecolumn BETWEEN  DATE_SUB(NOW(), INTERVAL 2 WEEK) AND NOW() ORDER BY recieved DESC";

Upvotes: 2

Zeedia
Zeedia

Reputation: 1503

Try this 

$notifications_query= "SELECT * FROM notifications WHERE viewed BETWEEN START_DATE AND END_DATE ";

received definition must be date in order for the query to work.

Upvotes: 0

Stephan
Stephan

Reputation: 17991

You can use a where clause that contains the datediff() function explained here

http://www.w3schools.com/sql/func_datediff.asp

Upvotes: 1

X3no
X3no

Reputation: 442

WHERE viewed BETWEEN (NOW() - INTERVAL 14 DAY) AND now()

Upvotes: 2

Michael
Michael

Reputation: 1231

I think this should do the trick:

$twoweeksago = time()- (3600*24*14);
$notifications_query= "SELECT * FROM notifications WHERE viewed > {$twoweeksago}  ORDER BY recieved DESC";

Upvotes: 0

Related Questions