Working with Dates and Times - MySQL - page 231

DELETE FROM mytbl WHERE expire_date < NOW ();
Comparing times to one another
Comparisons involving times are similar to those involving dates. For example, to find
times in the t1 column that occurred from 9 AM to 2 PM, use an expression like one
of these:
WHERE t1 BETWEEN '09:00:00' AND '14:00:00';
WHERE HOUR(t1) BETWEEN 9 AND 14;
For an indexed TIME column, the first method is more efficient. The second method has
the property that it works not only for TIME columns, but for DATETIME and TIME
STAMP columns as well.
Comparing dates to calendar days
To answer questions about particular days of the year, use calendar-day testing. The
following examples illustrate how to do this in the context of looking for birthdays:
• Who has a birthday today? This requires matching a particular calendar day, so you
extract the month and day but ignore the year when performing comparisons:
WHERE MONTH(d) = MONTH(CURDATE()) AND DAYOFMONTH(d) = DAYOFMONTH(CURDATE());
This kind of statement commonly is applied to biographical data to find lists of
actors, politicians, musicians, and so forth, who were born on a particular day of
the year.
It's tempting to use DAYOFYEAR() to solve “on this day” problems because it results
in simpler statements. But DAYOFYEAR() doesn't work properly for leap years. The
presence of February 29 throws off the values for days from March through De‐
cember.
• Who has a birthday this month? In this case, it's necessary to check only the month:
WHERE MONTH(d) = MONTH(CURDATE());
• Who has a birthday next month? The trick here is that you can't just add one to the
current month to get the month number that qualifying dates must match. That
gives you 13 for dates in December. To make sure that you get 1 (January), use either
of the following techniques:
WHERE MONTH(d) = MONTH(DATE_ADD(CURDATE(),INTERVAL 1 MONTH));
WHERE MONTH(d) = MOD(MONTH(CURDATE()),12)+1;