ProblemYou want to select rows based on temporal constraints. SolutionUse a date or time condition in the WHERE clause. This may be based on direct comparison of column values with known values. Or it may be necessary to apply a function to column values to convert them to a more appropriate form for testing, such as using MONTH( ) to test the month part of a date. DiscussionMost of the preceding date-based techniques were illustrated by example statements that produce date or time values as output. You can use the same techniques in WHERE clauses to place date-based restrictions on the rows selected by a statement. For example, you can select rows by looking for values that occur before or after a given date, within a date range, or that match particular month or day values. Comparing dates to one anotherThe following statements find rows from the date_val table that occur either before 1900 or during the 1900s: mysql> SELECT d FROM date_val where d < '1900-01-01'; +------------+ | d | +------------+ | 1864-02-28 | +------------+ mysql> SELECT d FROM date_val where d BETWEEN '1900-01-01' AND '1999-12-31'; +------------+ | d | +------------+ | 1900-01-15 | | 1987-03-05 | | 1999-12-31 | +------------+ When you don't know the exact date you want for a WHERE clause, you can often calculate it using an expression. For example, to perform an "on this day in history" statement to search for rows in a table history to find events occurring exactly 50 years ago, do this: SELECT * FROM history WHERE d = DATE_SUB(CURDATE(),INTERVAL 50 YEAR); You see this kind of thing in newspapers that run columns showing what the news events were in times past. (In essence, the statement identifies those events that have reached their n-th anniversary.) If you want to retrieve events that occurred "on this day" for any year rather than "on this date" for a specific year, the statement is a bit different. In that case, you need to find rows that match the current calendar day, ignoring the year. That topic is discussed in "Comparing dates to calendar days" later in this recipe. Calculated dates are useful for range testing as well. For example, to find dates that occur within the last seven years, use DATE_SUB( ) to calculate the cutoff date: mysql> SELECT d FROM date_val WHERE d >= DATE_SUB(CURDATE(),INTERVAL 7 YEAR); +------------+ | d | +------------+ | 1999-12-31 | | 2000-06-04 | +------------+ Note that the expression in the WHERE clause isolates the date column d on one side of the comparison operator. This is usually a good idea; if the column is indexed, placing it alone on one side of a comparison enables MySQL to process the statement more 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 7 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, either of the following will do: ...WHERE d >= '1987-01-01' AND d <= '1991-12-31'; ...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 of ways:
Comparing times to one anotherComparisons involving times are similar to those involving dates. For example, to find times that occurred from 9 AM to 2 PM, use an expression like one of the following: ...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 would be more efficient. The second method has the property that it works not only for TIME columns, but for DATETIME and TIMESTAMP columns as well. Comparing dates to calendar daysTo 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:
|