How to Retrieve Data Within a Date Range in MySQL

1. Retrieve Data Within a Date Range in MySQL
2. Use the SELECT, WHERE, and BETWEEN Clause to Query a Date Column Within a Range in MySQL
3. Use the >= and <= Comparison Operators to Query a Date Column Within a Range in MySQL
4. Use a Recursive Date Range Generator Method to Query a Date Column Within a Range in MySQL

This tutorial demonstrates how to query a database table between two dates using these three methods.

Retrieve Data Within a Date Range in MySQL

MySQL offers a certain level of convenience for working with dates via the date and time data types, which can combine to form a datetime or timestamp.

When working with a date column, various comparison methods can combine with the SELECT and WHERE clauses to effectively retrieve needed data within a date range.

1. Use the BETWEEN clause.
2. Use other comparison operators like the <, >, <=, and >=.
3. Use a date range generator combined with an INNER JOIN.

Use the SELECT, WHERE, and BETWEEN Clause to Query a Date Column Within a Range in MySQL

The SELECT-WHERE-BETWEEN clause is an efficient tool for filtering a result-set in MySQL. The BETWEEN keyword is the most relevant to this tutorial.

However, it requires the specification of a lower and upper limit on values to be retrieved.

For example, let us create a sample database called registration_db with a registered_persons table of three columns and five records.

CREATE DATABASE registration_db;
USE registration_db;

-- CREATE TABLE
CREATE TABLE registered_persons(
	id INT AUTO_INCREMENT,
    name VARCHAR (255),
    date_registered DATE,

    PRIMARY KEY(id)
);
-- POPULATING TABLE
INSERT INTO registered_persons (name, date_registered) VALUES
    ("Mike Hannover","2019-10-24"),
    ("June Popeyes", "2019-10-30"),
    ("David Craigson", "2019-11-02"),
    ("Eleanor Roosenotvelt", "2019-11-03"),
    ("Albert Undsteiner", "2019-11-28");

-- PREVIEW TABLE
SELECT * FROM registered_persons;

Output:

id	name					date_registered
1	Mike Hannover			2019-10-24
2	June Popeyes			2019-10-30
3	David Craigson			2019-11-02
4	Eleanor Roosenotvelt	2019-11-03
5	Albert Undsteiner		2019-11-28

Now, let us query the database for details of persons registered between October 25, 2019, and November 03, 2019.

SELECT * FROM registered_persons
WHERE date_registered
BETWEEN "2019-10-25" AND "2019-11-03";

Output:

id	name					date_registered
2	June Popeyes			2019-10-30
3	David Craigson			2019-11-02
4	Eleanor Roosenotvelt	2019-11-03

Notice that the person registered on November 03, 2019, was included in the result-set. We get this because the BETWEEN clause is lower and upper limit inclusive.

Also, the query date must be from a lower range to a higher one. The query will return null values otherwise.

Use the >= and <= Comparison Operators to Query a Date Column Within a Range in MySQL

Comparison operators like <, >, <=, and >= can replicate the operation of the BETWEEN clause, as previously illustrated.

Let us replicate the previous query using >= AND <= (min and max range values inclusive, as with the BETWEEN clause).

SELECT * FROM registered_persons
WHERE date_registered >= "2019-10-25" AND date_registered <= "2019-11-03";

/* The WHERE query can also be written in this form to replicate BETWEEN
WHERE "2019-10-25" <= date_registered AND date_registered <= "2019-11-03";
*/

Output:

id	name					date_registered
2	June Popeyes			2019-10-30
3	David Craigson			2019-11-02
4	Eleanor Roosenotvelt	2019-11-03

The result is the same, as expected. The only downside to using these comparison operators for such operation is the repetition of query parameters, as date_registered repeats twice.

Here is the official documentation on comparison operators for further reference.

Use a Recursive Date Range Generator Method to Query a Date Column Within a Range in MySQL

Another approach to filtering results within a range is via a recursive date range generator, but it is computationally expensive.

First, we generate a temporary table containing all the dates within the defined range. Then we filter the target table with the generated temporary table via an inner join.

This approach is slightly more complicated than the previous examples, but it may be relevant for some use-cases.

-- Using a recursive call to generate date elements
WITH RECURSIVE date_range AS (
    SELECT '2019-10-25' AS date     -- start value
   	UNION ALL
   	SELECT date + INTERVAL 1 day    -- increment by one day
   	FROM date_range
   	WHERE date < '2019-11-03')      -- stop date

SELECT id, name, date_registered
FROM registered_persons
INNER JOIN date_range
ON date_registered = date;

Output:

id	name					date_registered
2	June Popeyes			2019-10-30
3	David Craigson			2019-11-02
4	Eleanor Roosenotvelt	2019-11-03

All the examples illustrated in this tutorial are also applicable to columns implementing a datetime or timestamp datatype. In such cases, use the DATE() or DATEPART() function to first extract the date component before applying the comparison operators.

Here is an official reference for date extraction methods in MySQL.