CODEWITHSUNDEEP
mysqlsql
Jerry Jones
Jerry Jones

Reputation: 796

Get all rows before a specific day

We are using the below updated SQL to get customers list from our db whom we send SMS before 3 days.

SELECT * FROM sms WHERE sent_time >= NOW() - INTERVAL 3 DAY;

The table sms is updated daily along with the sent_time column with default value of 0 or the last sent time.

There are rows with the value of sent_time = 0 but no row is fetched by the above script.

What is the correct SQL?

Earlier we were using the SQL with php like mentioned below:

$vTime = time() - ( 60*60*24*3 );
$sql = "SELECT * FROM sms WHERE $vTime <= sent_time";

Upvotes: 1

Views: 89

Answers (4)

oshell
oshell

Reputation: 9123

The function NOW() will return current date and time, but as I can see you have used PHP time() before, which returns a Unix-Timestamp. The SQL equivalent is UNIX_TIMESTAMP().

Syntax UNIX_TIMESTAMP()

SELECT * FROM sms WHERE sent_time >= UNIX_TIMESTAMP() - (60*60*24*3);

Syntax UNIX_TIMESTAMP(date)

SELECT * FROM sms WHERE sent_time >= UNIX_TIMESTAMP(NOW() - INTERVAL 3 DAY) OR sent_time = 0

Upvotes: 1

Vishnu Sharma
Vishnu Sharma

Reputation: 642

Try below:

SELECT * FROM sms WHERE sent_time <= DATE_SUB(NOW(), INTERVAL 3 DAY) OR sent_time=0;

Upvotes: 0

kitensei
kitensei

Reputation: 2530

NOW() - INTERVAL 3 DAY; returns a DATETIME while echo time() - ( 60*60*24*3 ); returns a timestamp.

If your database column is a timestamp, your MySQL test will never work, use this instead:

SELECT * FROM sms WHERE sent_time >= UNIX_TIMESTAMP(NOW() - INTERVAL 3 DAY)

Upvotes: 1

Sanjay Chaudhari
Sanjay Chaudhari

Reputation: 420

Just change select query..

SELECT * FROM sms WHERE sent_time >= DATE_SUB(NOW(), INTERVAL 3 DAY) AND NOW();

Upvotes: 0

Related Questions