MySQL Cookbook

5.31 Selecting Records Based on Their Temporal Characteristics

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:

• Store a date in each record indicating when it was created. (Do this by making the column a TIMESTAMP or by setting it to NOW( ); see Recipe 5.34 for details.) To perform an expiration operation later, determine which records have a creation date that is too old by comparing that date to the current date. For example, the query to expire records that were created more than n days ago might look like this:

  DELETE FROM tbl_name WHERE create_date < DATE_SUB(NOW( ),INTERVAL n DAY);

• Store an explicit expiration date in each record by calculating the expiration date with DATE_ADD( ) when the record is created. For a record that should expire in n days, you can do that like this:

  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:

• 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 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.

• 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 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;