How to get list of dates between two dates in mysql select query

sql

recursive-queries

date-range

performance-optimization

byAnton Shumikhin·Nov 26, 2024

Generate a list of dates between two specific dates using the below recursive CTE in MySQL:

WITH RECURSIVE DateSeries AS (
  SELECT '2023-01-01' AS target_date -- Our start date, also the best way to kick-off the New Year!
  UNION ALL
  SELECT target_date + INTERVAL 1 DAY FROM DateSeries
  WHERE target_date < '2023-01-10' -- Our end date, buffer before those resolutions start to fail!
)
SELECT target_date FROM DateSeries;

Replace '2023-01-01' and '2023-01-10' with your chosen start and end dates respectively.

Digging deeper: Generating more than just a day's range

The example uses MySQL 8.0's powerful Recursive CTEs functionality to traverse through a list of dates with ease. But what if you need more than just a day's range? Just tweak the INTERVAL value for longer durations.

But hold up, what if you're using a legacy version of MySQL? Not a problem! Use a number sequence table or calendar table instead. Here’s an easy-to-implement query for that:

SELECT DATE_ADD('2023-01-01', INTERVAL a.i + 10 * b.i + 100 * c.i DAY) AS Date
FROM (SELECT 0 i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) a /*Nine more options than a cat's life! */
JOIN (SELECT 0 i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) b /* Well, here be two cats! */
JOIN (SELECT 0 i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) c /* And a third one to make it a triplet. No superstitions today! */
WHERE DATE_ADD('2023-01-01', INTERVAL a.i + 10 * b.i + 100 * c.i DAY) BETWEEN '2023-01-01' AND '2023-12-31'
ORDER BY Date;

Dodging infinite loops with MAX_RECURSION

Recursive queries can get stuck in infinite loops. Don't let these loops hold you hostage! Fight back using the MAX_RECURSION limit option.

Generating extensive date ranges gets easier with a handy date dimension table. It's like a pre-filled bag of time turners from Harry Potter!

Juggling leap years and time zones

Like in a circus, leap years and time zones can throw your queries off balance. Ensure your query accommodates these variations for flawless execution.

Query customization: Meeting specific requirements

Queries are as diverse as humanity! To catch only weekdays or exclude holidays, adjust the logic within the recursive CTE or join additional tables.

Performance optimization: Efficiency for the win!

For a data ninja, efficiency is key. Pre-generating dates and suitable indexing can speed up computation time, especially under heavy demand.

Large date ranges: Subquery and join strategy

Brace yourself for large date ranges! A subquery with cross joins becomes your best bet. Remember, the right indexes and partitioning strategies boost performance.

explain-codes / Sql / How to get list of dates between two dates in mysql select query

Linked

Getting a date list in a range in PostgreSQL



Get all dates between two dates in SQL Server



Generate a resultset of incrementing dates in T-SQL



Mysql add days to a date



Mysql's now() +1 day

