Working with Dates and Times - MySQL - page 230

efficiently. To illustrate, the preceding WHERE clause can be written in a way that's logically
equivalent but much less efficient for MySQL to execute:
WHERE DATE_ADD(d,INTERVAL 20 YEAR) >= CURDATE();
Here, the d column is used within an expression. That means every row must be retrieved
so that the expression can be evaluated and tested, which makes any index on the column
useless.
Sometimes it's not so obvious how to rewrite a comparison to isolate a date column on
one side. For example, the following WHERE clause uses only part of the date column in
the comparisons:
WHERE YEAR(d) >= 1987 AND YEAR(d) <= 1991;
To rewrite the first comparison, eliminate the YEAR() call, and replace its right side with
a complete date:
WHERE d >= '1987-01-01' AND YEAR(d) <= 1991;
Rewriting the second comparison is a little trickier. You can eliminate the YEAR() call
on the left side, just as with the first expression, but you can't just add -01-01 to the year
on the right side. That produces the following result, which is incorrect:
WHERE d >= '1987-01-01' AND d <= '1991-01-01';
That fails because dates from 1991-01-02 to 1991-12-31 fail the test, but should pass.
To rewrite the second comparison correctly, do this:
WHERE d >= '1987-01-01' AND d < '1992-01-01';
Another use for calculated dates occurs frequently in applications that create rows that
have a limited lifetime. Such applications must be able to determine which rows to delete
when performing an expiration operation. You can approach this problem a couple
ways:
• Store a date in each row indicating when it was created. (Do this by making the
column a TIMESTAMP or by setting it to NOW() ; see Recipe 6.7 for details.) To perform
an expiration operation later, determine which rows have a creation date that is too
old by comparing that date to the current date. For example, the statement to expire
rows that were created more than n days ago might look like this:
DELETE FROM mytbl WHERE create_date < DATE_SUB ( NOW (), INTERVAL n DAY );
• Store an explicit expiration date in each row by calculating the expiration date with
DATE_ADD() when the row is created. For a row that should expire in n days, do this:
INSERT INTO mytbl ( expire_date ,...)
VALUES ( DATE_ADD ( NOW (), INTERVAL n DAY ),...);
To perform the expiration operation in this case, compare the expiration dates to
the current date to see which have been reached: