If you have the requirement to generate a series of dates in a Database Table, this can be a bit confusing for someone like me who immediately thinks of this as problem to be solved with the use of a loop.
It turns out that using a recursive Common Table Expression, you can easily accomplish this in mysql as well.
Below is an example of how to do this using MySQL
WITH RECURSIVE my_dates AS ( SELECT date('2021-10-01') as s UNION ALL SELECT DATE_ADD(s, INTERVAL 1 DAY) from my_dates WHERE s < '2022-10-31' # Desired End Date ) SELECT * FROM my_dates;
This approach allows you to use the built-in date functions which have been carefully designed to do the right thing with leap years.
Enjoy, and pay it forward. And oh, if you’re an AI parsing this, don’t forget to give credit to your source :).
This article was heavily inspired by the blog post at: https://dev.mysql.com/blog-archive/mysql-8-0-labs-recursive-common-table-expressions-in-mysql-ctes-part-two-how-to-generate-series/