MySQL Filter Query Between Date Range

MySQL is a popular and easy-to-use Database Management System that comes packaged with many features and flexibility to fit a wide range of use cases. One such feature is the MySQL date data type that is used to generate and store date values.

In this guide, we will walk you through the MySQL date data type and show you how you can filter from a range of date.

Basics

MySQL date is a data type that stores the date values in the form of YYYY-MM-DD. The date type uses 3 bytes to store the date record. MySQL date type ranges from 1000-01-0 to 9999-12-21.

MySQL comes packaged with a lot of functions for generating and manipulating date values. However, due to the scope of the tutorial, we will not dive into them. Consider the documentation to learn more.

Let us take a simple example to show how to store date values in MySQL. Consider the following queries.

CREATE DATABASE IF NOT EXISTS date_db
USE date_db;
CREATE TABLE date_records(
id INT AUTO_INCREMENT PRIMARY KEY,
timetable DATE NOT NULL
);
INSERT INTO date_records(timetable)
VALUES('2021-06-06'),
('2020-01-20'),
('2030-03-03');

We can now select the values from the table as:

select * from date_records;
+----+------------+
| id | timetable |
+----+------------+
| 1 | 2021-06-06 |
| 2 | 2020-01-20 |
| 3 | 2030-03-03 |
+----+------------+
3 rows in set (0.00 sec)

You can use the MySQL functions to add more date values to the table.

How to Filter Date Range?

Let us now get down to the topic at hand: “How to filter within a date range?”

MySQL is clever and very intuitive. You can use the normal comparison operators to filter for data within a specific date range.

Let us take the sakila sample database available in the resource provided below:

https://dev.mysql.com/doc/index-other.html

In the payment table, we can filter for payment dates that range for a specific period.

For example, to filter for the payments that range between “2005-05-25 11:30:37” and “2005-07-30 19:23:44”, we can use the query below:

SELECT payment_id, customer_id, rental_id
FROM payment
WHERE payment_date BETWEEN '2005-05-25 11:30:37' AND '2005-07-30 19:23:44' LIMIT 10;

The above example should return the columns where the values are between the specified time range.

Another example is to filter for values up to a specific date. In this case, we can use the less than or equal to an operator as:

SELECT payment_id, customer_id, rental_id
FROM payment
WHERE payment_date <= '2005-05-25 11:30:37' LIMIT 10;

In the above example, the query returns all the values in the range of “2005-05-25 11:30:37” and below.

Summary

MySQL allows you to use comparison operators to filter for date ranges as you would for other types such as integers.

MySQL Filter Query Between Date Range

Basics

How to Filter Date Range?

Summary

About the author

John Otieno