problem is select in mysql

Question

hi my friends 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

Answer 1

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

Answer 2

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)

Answer 3

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.

Answer 4

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