CODEWITHSUNDEEP
phpmysql
Joshua Greenhough
Joshua Greenhough

Reputation: 81

timestamp query not retrieving results?

Hi there :) I am doing a query, and it will pull results where the timestamp is more than 30 days old. Here's what I've put so far:

$result = $db->query("SELECT time FROM table1 WHERE open_time < (NOW() - INTERVAL 30 DAYS)");

So as you can see, it will try to find results more than 30 days old, however when I execute the query, it doesn't select any rows, even though the column field "open_time" is more than 30 days old.

Any suggestions I do greatly appreciate :)

Upvotes: 0

Views: 71

Answers (2)

RaMeSh
RaMeSh

Reputation: 3424

Try this Query

 $result = $db->query("SELECT time FROM table1 WHERE DATE(`date`) = DATE(NOW() - INTERVAL 30
     DAY")

or

try this one also for your reference

timestampadd()

SELECT * FROM table WHERE `date` > timestampadd(day, -30, now());

try this link also

Upvotes: 1

Theo Babilon
Theo Babilon

Reputation: 661

Have you tried to cast the date field?

$result = $db->query("SELECT time FROM table1 WHERE CAST(open_time AS DATE) < (NOW() - INTERVAL 30 DAYS)");

EDIT:

Maybe you should use this tricky query:

$result = $db->query("SELECT time FROM table1 WHERE open_time <
UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 30 DAY))"):

Upvotes: 1

Related Questions