MySQL Cookbook, 2nd Edition

Selecting Rows Based on Their Temporal Characteristics

Problem

You want to select rows based on temporal constraints.

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.

Discussion

Most 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 another

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