2

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

5

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 !!

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.