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.
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 TABLE date_records(
id INT AUTO_INCREMENT PRIMARY KEY,
timetable DATE NOT NULL
INSERT INTO date_records(timetable)
We can now select the values from the table as:
| 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:
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:
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:
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.
MySQL allows you to use comparison operators to filter for date ranges as you would for other types such as integers.