problem is select in mysql

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开发者_Python百科

how i can do that

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);

you can count time in PHP via time()-(7*86400) and use that value?

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.

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)