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.
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:
The above example should return the columns where the values are between the specified time range.
<img alt="" data-lazy- data-lazy-src="https://kirelos.com/wp-content/uploads/2021/08/echo/mysql-filter-query-between-date-range-01.png" data-lazy- height="368" src="data:image/svg xml,” width=”766″>
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;
<img alt="" data-lazy- data-lazy-src="https://kirelos.com/wp-content/uploads/2021/08/echo/mysql-filter-query-between-date-range-02.png" data-lazy- height="362" src="data:image/svg xml,” width=”758″>
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.
About the author
<img alt="" data-del="avatar" data-lazy-src="https://kirelos.com/wp-content/uploads/2021/08/echo/john-150×150.png61213d462e98f.jpg" height="112" src="data:image/svg xml,” width=”112″>
John Otieno
My name is John and am a fellow geek like you. I am passionate about all things computers from Hardware, Operating systems to Programming. My dream is to share my knowledge with the world and help out fellow geeks. Follow my content by subscribing to LinuxHint mailing list
