0

I have this table:

id, title, views, date (timestamp)

I'm trying to select the most viewd posts from a specific period of time (a week from two months ago, for example)

This is my query right now but it doesn't seem to be working:

SELECT * FROM table
WHERE date > DATE_SUB( DATE_ADD(NOW(), INTERVAL -2 MONTH), INTERVAL 1 WEEK)
ORDER by views DESC
LIMIT 10

Here's a similar question how to select a period of time in mysql?

but I don't want to especify the period with a fixed date, i want it to be relative to the excution of the script.

Any ideas?

Community
  • 1
  • 1
dythffvrb
  • 177
  • 1
  • 9
  • Anything help in this question/answer: http://stackoverflow.com/questions/5182275/datetime-equal-or-greater-than-today-in-mysql? In particular `CURDATE()`. – summea Mar 04 '12 at 01:54
  • I think this question is better http://stackoverflow.com/questions/2642885/display-rows-from-mysql-where-a-datetime-is-within-the-next-hour but I'm still a noob when it comes to mysql I can't adapt it to my needs. – dythffvrb Mar 04 '12 at 01:59

1 Answers1

0

Try this and let me know if you have any issue:

SELECT * FROM stats
WHERE date between
  DATE_SUB(NOW(), INTERVAL 2 MONTH) and
  DATE_ADD(DATE_SUB(NOW(), INTERVAL 2 MONTH), INTERVAL 1 WEEK)
ORDER by views DESC
LIMIT 10
Mosty Mostacho
  • 42,742
  • 16
  • 96
  • 123