CODEWITHSUNDEEP
mysql
NaughtySquid
NaughtySquid

Reputation: 2097

Get mysql rows where date timestamp is 30 days or newer

I am trying to get a bunch of mysql rows from the last 30 days using this sql:

SELECT * FROM `articles` WHERE date >= DATE(NOW() - INTERVAL 30 DAY) ORDER BY `views` DESC

I am obviously doing something wrong as this returns rows from last year.

My date field in a gmt timestamp.

Upvotes: 1

Views: 3623

Answers (3)

Juan Pablo Califano
Juan Pablo Califano

Reputation: 12333

Try this:

SELECT * 
FROM `articles` 
WHERE date >= DATE_SUB(NOW(),INTERVAL 30 DAY) 
ORDER BY `views` DESC

Upvotes: 0

ceejayoz
ceejayoz

Reputation: 180014

If your date field is not a DATETIME, TIMESTAMP, or DATE field type (as I think you're indicating with "in a GMT timestamp" - I suspect you're storing the integer itself), you're not going to be able to do comparison like that. You'd need to convert the 30 days ago date into a timestamp too.

WHERE date >= UNIX_TIMESTAMP(DATE(NOW() - INTERVAL 30 DAY))

Upvotes: 4

CodeBird
CodeBird

Reputation: 3858

try:

SELECT * FROM `articles` WHERE date >= CURDATE() - INTERVAL 30 DAY 
ORDER BY `views` DESC

Upvotes: 0

Related Questions