CODEWITHSUNDEEP
phpmysql
jartaud
jartaud

Reputation: 385

Help with MySQL date operation

Hello i have two columns: duration (int) and dateCol (date) in a table. How should i proceed if i want to know when CURDATE() - dateCol will iqual to duration.

ie: CURDATE() - dateCol (2010-05-08) = duration (14 days), and then send an e-amil?

Upvotes: 2

Views: 100

Answers (2)

Mark Byers
Mark Byers

Reputation: 839114

To do what you want you can use DATEDIFF: 

DATEDIFF(NOW(), last_email_date) >= 14

An alternative method is to interval subtraction:

CURDATE() - interval 14 day >= last_email_date

The second can be faster if there is a suitable index.

Note that I've chosen to use an inequality test instead of an equality because this will handle missed emails if your process is offline for one day and is restarted the next day. You should mark the row as handled when you've handled it to prevent more than one email being sent. This will also prevent multiple emails being sent if your process runs more than once per day (this could happen if you restart it to upgrade / bug fix).

Upvotes: 2

Ignacio Vazquez-Abrams
Ignacio Vazquez-Abrams

Reputation: 799420

DATE_SUB(CURDATE(), INTERVAL 14 DAY) = datecol

Upvotes: 3

Related Questions