Have an account? Sign in
Login  Register  Facebook
problem with mysql select rows In a date range
i\'am tring to select from \'forum_topics\' the topics that has been added from 7 days ago and the date field name is \'topic_date\'
please help me
Started: September 15, 2011 Latest Activity: September 15, 2011 php mysql select date
2 Answers
sure this will work
SELECT * FROM `forum_topics` WHERE from_unixtime(topic_date) >= SUBDATE(NOW(),7)

Posted: is_set
In: September 15, 2011

See what SUBDATE does - it takes its 2. parameter as days by default. It also produces a datetime type in this case, you seem to just have a unix timestamp.
mysql> SELECT  SUBDATE(NOW(),604800);
+-----------------------+
| SUBDATE(NOW(),604800) |
+-----------------------+
| 0354-01-27 12:31:30   |
+-----------------------+
You\'d want
SELECT * FROM `forum_topics`
	 WHERE from_unixtime(topic_date) > SUBDATE(NOW(),interval 604800 second);
or
SELECT * FROM `forum_topics` WHERE from_unixtime(topic_date) > SUBDATE(NOW(),7)
or
SELECT * FROM `forum_topics` WHERE  topic_date > (UNIX_TIMESTAMP() - 604800);

Posted: MacOS
In: September 15, 2011

Your Answer

xDo you want to answer this question? Please login or create an account to post your answer