|||

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).

Up next The Problem With GTD I’m a fan of David Allen’s Getting Things Done, but it suffers from one major shortcoming, at least for me: it offers some great methods for Converting to Project Connection Across Multiple Packages in SSIS 2012 I’m migrating a Business Intelligence project from SQL Server 2005 to SQL Server 2012. Microsoft has, overall, done a great job with their
Latest posts Meta Work Some Thoughts on Report Usability Yachats Sunset BIML Banana ooo na na Super Love Scroll Lock Why We Encrypt UK government quietly rewrites hacking laws to give GCHQ immunity I’m Terrified Right Now You Do Not Need Permission The Reward For Good Work How a Microwave Should Work One in a Million is Next Tuesday What I Want From Tech Support Windows Batch Gotcha: Use REM Inside IF Blocks Great Advice From Larry Wall A Haiku Time Limits on Browser Plugins? Better Questions Lessons in Bug Hunting /time Shipped! Recipe: Tuna Salad Gun Control and Strong Encryption Day-of-Week Differences in MySQL and MS SQL Server Washingsoft UAnix Microsoft Orifice The Right Question Converting to Project Connection Across Multiple Packages in SSIS 2012 Generating a Range of Dates in MySQL