5.31.1 Problem
You want to select records based on temporal constraints.
5.31.2 Solution
Use 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.
5.31.3 Discussion
Most of the preceding date-based techniques were illustrated by example queries that produce date or time values as output. You can use the same techniques in WHERE clauses to place date-based restrictions on the records selected by a query. For example, you can select records occurring before or after a given date, within a date range, or that match particular month or day values.
5.31.4 Comparing Dates to One Another
The following queries find records 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 | +------------+
If your version of MySQL is older then 3.23.9, one problem to watch out for is that BETWEEN sometimes doesn't work correctly with literal date strings if they are not in ISO format. For example, this may fail:
SELECT d FROM date_val WHERE d BETWEEN '1960-3-1' AND '1960-3-15';
If that happens, try rewriting the dates in ISO format for better results:
SELECT d FROM date_val WHERE d BETWEEN '1960-03-01' AND '1960-03-15';
You can also rewrite the expression using two explicit comparisons:
SELECT d FROM date_val WHERE d >= '1960-03-01' AND d <= '1960-03-15';
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" query to search for records 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 query compiles 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 query is a bit different. In that case, you need to find records that match the current calendar day, ignoring the year. That topic is discussed in Recipe 5.31.6 later in this section.
Calculated dates are useful for range testing as well. For example, to find dates that occur within the last six years, use DATE_SUB( ) to calculate the cutoff date:
mysql> SELECT d FROM date_val WHERE d >= DATE_SUB(CURDATE( ),INTERVAL 6 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 allows MySQL to process the query 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 6 MONTH) >= 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 righthand 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 lefthand side, just as with the first expression, but you can't just add -01-01 to the year on the righthand side. That would produce 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 records that have a limited lifetime. Such applications must be able to determine which records to delete when performing an expiration operation. You can approach this problem a couple of ways:
DELETE FROM tbl_name WHERE create_date < DATE_SUB(NOW( ),INTERVAL n DAY);
INSERT INTO tbl_name (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 ones have been reached:
DELETE FROM tbl_name WHERE expire_date < NOW( )
5.31.5 Comparing Times to One Another
Comparisons 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.
5.31.6 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:
... WHERE MONTH(d) = MONTH(CURDATE( )) AND DAYOFMONTH(d) = DAYOFMONTH(CURDATE( ));
This kind of query commonly is applied to biographical data to find lists of actors, politicians, musicians, etc., 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 queries. But DAYOFYEAR( ) doesn't work properly for leap years. The presence of February 29 throws off the values for days from March through December.
... WHERE MONTH(d) = MONTH(CURDATE( ));
... WHERE MONTH(d) = MONTH(DATE_ADD(CURDATE( ),INTERVAL 1 MONTH)); ... WHERE MONTH(d) = MOD(MONTH(CURDATE( )),12)+1;
Using the mysql Client Program
Writing MySQL-Based Programs
Record Selection Techniques
Working with Strings
Working with Dates and Times
Sorting Query Results
Generating Summaries
Modifying Tables with ALTER TABLE
Obtaining and Using Metadata
Importing and Exporting Data
Generating and Using Sequences
Using Multiple Tables
Statistical Techniques
Handling Duplicates
Performing Transactions
Introduction to MySQL on the Web
Incorporating Query Resultsinto Web Pages
Processing Web Input with MySQL
Using MySQL-Based Web Session Management
Appendix A. Obtaining MySQL Software
Appendix B. JSP and Tomcat Primer
Appendix C. References