now i have table call files its have time has this stamp 1260606325 now i tring to get the files which added in the last 7 day
SELECT * FROM `files` WHERE time > SUBDATE(NOW(),604800)
its return zero
how i can do that
-
Use some punctuation marks in yous sentences.Pawka– Pawka2009-12-16 11:33:47 +00:00Commented Dec 16, 2009 at 11:33
Add a comment
|
4 Answers
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 `files` WHERE from_unixtime(time) > SUBDATE(NOW(),interval 604800 second);
or
SELECT * FROM `files` WHERE from_unixtime(time) > SUBDATE(NOW(),7)
or
SELECT * FROM `files` WHERE time > (UNIX_TIMESTAMP() - 604800);
1 Comment
Felix Kling
+1 for reading the question more carefully than me and noticing that
time is of datatype TIMESTAMPyou can count time in PHP via time()-(7*86400) and use that value?
Comments
You should specify the unit in the second parameter of SUBDATE:
SELECT * FROM `files` WHERE time > SUBDATE(NOW(), INTERVAL 7 DAY);
If you do not specify a unit, days is default so 604800 should be 7.
Comments
Try this:
SELECT * FROM files WHERE time > SUBDATE(CURDATE(), 7)
or
SELECT * FROM files WHERE time > SUBDATE(NOW(), 7)
depending on what datatype time is of.
You can find information about all the date functions in MySQL here.
The default unit of the second parameter of SUBDATE is days:
SUBDATE(date,INTERVAL expr unit), SUBDATE(expr,days)