Problem
You need to retrieve a list of dates between two specified dates.
Input
First, let’s create a table and insert some sample data into it.
startDate | endDate |
---|---|
2023-01-01 | 2023-01-05 |
2023-02-01 | 2023-02-03 |
For this example, the table contains two date ranges: Jan 1 to Jan 5 and Feb 1 to Feb 3 of 2023.
Try Hands-On: HERE
Source Tables: Gist
Desired Solution
dateValue |
---|
2023-01-01 |
2023-01-02 |
2023-01-03 |
2023-01-04 |
2023-01-05 |
2023-02-01 |
2023-02-02 |
2023-02-03 |
Solution 1:
Using Recursive Approach
WITH RECURSIVE DateSeries AS (
SELECT startDate AS dateValue
FROM DateRange
UNION ALL
SELECT DATE_ADD(dateValue, INTERVAL 1 DAY)
FROM DateSeries
WHERE DATE_ADD(dateValue, INTERVAL 1 DAY) <= (SELECT endDate FROM DateRange WHERE DATE_ADD(dateValue, INTERVAL 1 DAY) BETWEEN startDate AND endDate)
)
SELECT DISTINCT dateValue
FROM DateSeries
ORDER BY dateValue;
Explanation:
- The recursive CTE starts by selecting the startDate from the DateRange table.
-
In the subsequent recursive part of the CTE (UNION ALL), we use DATE_ADD to add one day to the current dateValue
.-
The WHERE clause inside the recursive part ensures that the date addition only proceeds as long as the new date is still between the startDate and endDate of any row in the DateRange table - .
Finally, outside the CTE, we select the distinct dates to eliminate any duplicate date values and order the result by dat
ble.
Solution 2:
An alternative method to retrieve a list of dates between two dates in MySQL involves using a helper table (or number series). This approach uses a table that simply has a sequence of numbers, which we can use to generate a range of dates.
First, we’ll create a helper table named NumberSeries:
CREATE TABLE NumberSeries (n INT);
-- Let's insert a series of numbers. This will cover a range for up to 1000 days. You can increase it if needed.
DELIMITER ;
BEGIN
DECLARE i INT DEFAULT 0;
WHILE i < 1000 DO
INSERT INTO NumberSeries (n) VALUES (i);
SET i = i + 1;
END WHILE;
END;
DELIMITER ;
Solution:
SELECT DATE_ADD(dr.startDate, INTERVAL ns.n DAY) AS dateValue
FROM DateRange dr
JOIN NumberSeries ns
ON DATE_ADD(dr.startDate, INTERVAL ns.n DAY) BETWEEN dr.startDate AND dr.endDate
ORDER BY dateValue;
Explanation:
-
We use a JOIN between the DateRange table and the NumberSeries table.
-
The DATE_ADD function with the ns.n value from the NumberSeries table is used to add days to the startDate.
The BETWEEN condition in the ON clause ensures that the generated date falls within the range of startDate and endDate for each row in the DateRange table. -
Lastly, we order the results by the generated date (dateValue).
-
By using this solution, you can retrieve the list of dates between the start and end dates for each row in the DateRange table.