CODEWITHSUNDEEP
phpmysql
Mostafa Elkady
Mostafa Elkady

Reputation: 5801

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

how i can do that

Upvotes: 1

Views: 186

Answers (4)

nos
nos

Reputation: 229342

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

Upvotes: 4

Felix Kling
Felix Kling

Reputation: 817208

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)

Upvotes: 0

Tomas
Tomas

Reputation: 5143

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.

Upvotes: 1

dusoft
dusoft

Reputation: 11479

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

Upvotes: 1

Related Questions