Generating a Range of Dates in MySQL

Working on a report from a MySQL database, I needed a table of all dates for the next year. With SQL Server (2005 and later) there’s a CTE/recursive method to do this pretty elegantly, but I couldn’t find anything similar for MySQL. All the solutions I found involved temporary tables, loops, and/or stored procedures–none of these were viable options for me because it’s a production database and I can’t just go changing things. I needed a simple query, and since I couldn’t find one, I made one.

It’s ugly, but it did the job.

SELECT CAST((SYSDATE()+INTERVAL (H+T+U) DAY) AS date) d
FROM ( SELECT 0 H
    UNION ALL SELECT 100 UNION ALL SELECT 200 UNION ALL SELECT 300
  ) H CROSS JOIN ( SELECT 0 T
    UNION ALL SELECT  10 UNION ALL SELECT  20 UNION ALL SELECT  30
    UNION ALL SELECT  40 UNION ALL SELECT  50 UNION ALL SELECT  60
    UNION ALL SELECT  70 UNION ALL SELECT  80 UNION ALL SELECT  90
  ) T CROSS JOIN ( SELECT 0 U
    UNION ALL SELECT   1 UNION ALL SELECT   2 UNION ALL SELECT   3
    UNION ALL SELECT   4 UNION ALL SELECT   5 UNION ALL SELECT   6
    UNION ALL SELECT   7 UNION ALL SELECT   8 UNION ALL SELECT   9
  ) U
WHERE
  (SYSDATE()+INTERVAL (H+T+U) DAY) <= (SYSDATE()+INTERVAL 1 YEAR)

I just used that as a pseudo-table in my main query–boom! Done. I just needed one year, but of course you could tweak this to generate a longer range, start it from an arbitrary date, whatever you need.

I’m not a MySQL performance expert, but creating a persistent table with the date column as primary key might give better performance if that’s an option in your situation. Then again, this easily fits in-memory, so it might not make any difference. In any case, performance was acceptable for my purposes (ETL extract query to be run mainly during off-peak hours).