MySQL 8: Get a list of dates between two given dates

Introduction

Working with dates is a common scenario in software development. Whether you’re building a reservation system, an events calendar, or a report with a date range, you will eventually need to generate a list of dates between two given dates in MySQL. In this tutorial, you’ll learn how to efficiently create this range of dates using MySQL 8, which includes some handy SQL functions to make this process smooth.

Prerequisites

• MySQL Server 8.0 installed and running
• Basic knowledge of SQL syntax
• Access to a MySQL database with privileges to create tables and run queries

Using a Recursive Common Table Expression (CTE)

In MySQL 8, one of the simplest and most efficient ways to generate a list of dates between two dates is by using recursive Common Table Expressions (CTEs). CTEs are temporary result sets that can be referenced within a SELECT, INSERT, UPDATE, or DELETE statement. Recursive CTEs have the ability to call themselves, making them perfect for generating sequences of data such as a series of dates.

Here’s an example SQL query that uses a recursive CTE:

WITH RECURSIVE DateRange AS (
  SELECT '2023-01-01' AS StartDate
  UNION ALL
  SELECT DATE_ADD(StartDate, INTERVAL 1 DAY) FROM DateRange
  WHERE StartDate < '2023-01-31'
)
SELECT StartDate FROM DateRange;

The above query will generate a list of dates from January 1st, 2023, to January 31st, 2023. It begins with a base date and then adds one day incrementally until the upper limit date is reached.

Explanation of the Recursive CTE Query:

1. WITH RECURSIVE: This initiates the common table expression and indicates that it will be recursive.
2. DateRange AS: This defines the temporary set of results, or the CTE, and gives it a name (`DateRange` in this case).
3. SELECT ‘2023-01-01’ AS StartDate: This is the anchor member of the recursive CTE defining the starting date.
4. UNION ALL: This combines the base case with the recursive step, which is essential in recursive CTEs.
5. SELECT DATE_ADD(StartDate, INTERVAL 1 DAY): This is the recursive member that selects the StartDate from the previous level and adds one day to it.
6. FROM DateRange: This selects from the existing results of DateRange to continue the recursion.
7. WHERE StartDate < ‘2023-01-31’: This condition specifies the end of the recursion.

Output:

The output will be a list of all dates from January 1st, 2023 to January 31st, 2023, with each date listed in its row.

Handling Large Date Ranges

When generating a large list of dates, it’s important to be aware of the `max_recursion` limit, which defaults to 1000 in MySQL. If you’re trying to produce a list that exceeds this, you will need to temporarily override this setting:

SET @@cte_max_recursion_depth = 10000;

Once you’ve increased the `max_recursion` limit, follow the same CTE method described earlier to generate a large list of dates.

Advanced Usage: Parameterizing Queries

If you are looking to create a more dynamic query that can change based on user input or variable dates, you can parameterize your SQL query:

SET @StartDate := '2023-01-01';
SET @EndDate := '2023-01-31';

WITH RECURSIVE DateRange AS (
  SELECT @StartDate AS StartDate
  UNION ALL
  SELECT DATE_ADD(StartDate, INTERVAL 1 DAY) FROM DateRange
  WHERE StartDate < @EndDate
)
SELECT StartDate FROM DateRange;

By setting variables for the start and end dates, you can easily modify the query to use different dates without altering the SQL statement’s structure.

Generating Date Ranges with Stored Procedures

You might also consider encapsulating the logic of generating date ranges within a stored procedure, to make it reusable. Here’s an example of how you can do that:

DELIMITER //

CREATE PROCEDURE GenerateDateRange(IN start_date DATE, IN end_date DATE)
BEGIN
  WITH RECURSIVE DateRange AS (
    SELECT start_date AS StartDate
    UNION ALL
    SELECT DATE_ADD(StartDate, INTERVAL 1 DAY) FROM DateRange
    WHERE StartDate < end_date
  )
  SELECT StartDate FROM DateRange;
END;

//
DELIMITER ;

You would then call this stored procedure with the desired start and end dates:

CALL GenerateDateRange('2023-01-01', '2023-01-31');

Creating a stored procedure provides the benefits of not having to write extensive SQL queries in your application code and promoting reuse across different parts of your application that might need to generate date ranges.

Real-World Applications

Realistically, the list of dates is seldom used in isolation. In many cases, you’ll need to join it with other tables. For instance, suppose you have an events table and you need to find out which dates have events scheduled, and for the dates without any events, you still want them to be listed. Here’s how you could achieve this:

WITH RECURSIVE DateRange AS (
  SELECT '2023-01-01' AS StartDate
  UNION ALL
  SELECT DATE_ADD(StartDate, INTERVAL 1 DAY) FROM DateRange
  WHERE StartDate < '2023-01-31'
)
SELECT dr.StartDate, IFNULL(e.EventName, 'No Event') AS Event
FROM DateRange dr
LEFT JOIN Events e ON dr.StartDate = e.EventDate;

In the above query, we’re using a LEFT JOIN to merge the DateRange CTE with an ‘Events’ table on the condition of matching dates. If there’s no event for a given date, the result will show ‘No Event’.

Conclusion

In this tutorial, we explored several methods to retrieve a list of dates between two given dates in MySQL 8. From simple recursive CTEs to advanced stored procedures, you’ll now be able to generate and use date ranges effectively in your applications. Keep experimenting with these new queries, and you’ll become proficient in manipulating and displaying date information in any format required by your system.