Mysql – selecting records that are 3 days from expiring datetime

datedatetimeMySQL

I have a table that contains a datetime timestamp. What I am looking for is to select records that are exactly 3 days from the 30 day expiration based on the datetime timestamp.

Here is what I have so far although I don't think it is correct:

    SELECT jobs.*,occupations.title
    FROM jobs
    LEFT JOIN occupations on occupations.id = jobs.occupation_id
    WHERE DATE_ADD(jobs.`date_created`,INTERVAL 27 DAY) = DATE(NOW())

Help please 🙂

Best Answer

If date_created is a DATE, do this:

SELECT jobs.*,occupations.title
FROM jobs
LEFT JOIN occupations on occupations.id = jobs.occupation_id
WHERE jobs.`date_created` = DATE(NOW() - INTERVAL 27 DAY);

If date_created is DATETIME or TIMESTAMP and the time part is '00:00:00', do this:

SELECT jobs.*,occupations.title
FROM jobs
LEFT JOIN occupations on occupations.id = jobs.occupation_id
WHERE jobs.`date_created` >= DATE(NOW() - INTERVAL 27 DAY) + INTERVAL 0 SECOND;

These queries are better in terms of performance than the one in the question because the WHERE clauses of my queries will induce an index range scan, whereas the WHERE clause in the question will induce a full table scan (even if date_created is indexed).

If the jobs table does not have an index on date_created, then make one

ALTER TABLE jobs ADD INDEX (date_created);

Give it a Try !!

Related Question