MySQL Cookbook

Selecting Records Based on Their Temporal Characteristics

Problem

You want to select records 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 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.

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