Generate date range with MySQL

Time of time I need to generate reports in MySQL based on dates range. An intuitively obvious approach is to use some temporary table to build list of dates and build query based on that table.
But there is alternative way exists.
Using UNION with CROSS JOIN its possible to achieve same result without temporary tables and stored procedures.

To emulate a loop in MySQL from 1 to n we can use UNION n-times:

SELECT n FROM (SELECT 1 as n UNION SELECT 2) as LoopEmu;

This will return a result:


But what if we need to generate thousand of rows ? Repeating UNION thousand of times is not reasonable in that case. Here CROSS JOIN comes to help.
CROSS JOIN allows to multiply rows count as its work to cross join records each one to each one.
So for an example to get 25 rows we can use 8 unions only instead of 24:

SELECT @n:=@n+1 FROM 

(SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 ) q5

CROSS JOIN

(SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 ) q25,

(SELECT @n:=0) qInit;

To multiply rows count by 5 we need to add one more line only:

SELECT @n:=@n+1 FROM 

(SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 ) q5

CROSS JOIN

(SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 ) q25

CROSS JOIN 

(SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 ) q125,

(SELECT @n:=0) qInit;

Now here is few examples of date ranges queries:

Year To Day (YTD)
From: 1st day of current year
To: Current day

SELECT OnDate FROM
(
SELECT @DaysAgo:=@DaysAgo+1 as DaysAgo, Date(@EndDate:=@EndDate - INTERVAL 1 DAY) as OnDate FROM
  (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6) q6
  CROSS JOIN (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5) q30
  CROSS JOIN (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5) q150
  CROSS JOIN (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5) q750,
  (SELECT @StartDate:=Date(now()) + INTERVAL 365 DAY, @EndDate:=@StartDate, @DaysAgo:=0) qInit
) days750 WHERE OnDate Between DATE_FORMAT(NOW() ,'%Y-01-01') AND now();

Month To Day (MTD)
From: 1st day of current month
To: Current day
SELECT OnDate FROM
(
SELECT @DaysAgo:=@DaysAgo+1 as DaysAgo, Date(@EndDate:=@EndDate - INTERVAL 1 DAY) as OnDate FROM
  (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6) q6
  CROSS JOIN (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5) q30
  CROSS JOIN (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5) q150
  CROSS JOIN (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5) q750,
  (SELECT @StartDate:=Date(now()) + INTERVAL 365 DAY, @EndDate:=@StartDate, @DaysAgo:=0) qInit
) days750 WHERE OnDate Between DATE_FORMAT(NOW() ,'%Y-%m-01') AND now();

All days in current month
From: 1st day of current month
To: Last day of current month

SELECT OnDate FROM
(
SELECT @DaysAgo:=@DaysAgo+1 as DaysAgo, Date(@EndDate:=@EndDate - INTERVAL 1 DAY) as OnDate FROM
  (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6) q6
  CROSS JOIN (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5) q30
  CROSS JOIN (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5) q150
  CROSS JOIN (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5) q750,
  (SELECT @StartDate:=Date(now()) + INTERVAL 365 DAY, @EndDate:=@StartDate, @DaysAgo:=0) qInit
) days750 WHERE OnDate Between DATE_FORMAT(NOW() ,'%Y-%m-01') AND Last_Day(now());

Two months period
From: 1st day of previous month
To: Last day of next month

SELECT OnDate FROM
(
SELECT @DaysAgo:=@DaysAgo+1 as DaysAgo, Date(@EndDate:=@EndDate - INTERVAL 1 DAY) as OnDate FROM
  (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6) q6
  CROSS JOIN (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5) q30
  CROSS JOIN (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5) q150
  CROSS JOIN (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5) q750,
  (SELECT @StartDate:=Date(now()) + INTERVAL 365 DAY, @EndDate:=@StartDate, @DaysAgo:=0) qInit
) days750 WHERE OnDate Between DATE_FORMAT(NOW() - INTERVAL 1 MONTH ,'%Y-%m-01') AND Last_Day(now() + INTERVAL 1 MONTH);