MYSQL Generating a range of data for query purpose. In memory vs table.

We are about to compare two solutions to generate a set of dates for the need of query. We have seen two approach and we about to check which one perform better. The solution one was published by Jeff Garretson in 2012. Until now MySQL does not offer any build in function to render data lists. Second approach will be much cleaner and shorter but take longer to execute.

I whish we could see below query working but there is generate an error.
SELECT BETWEEN NOW() AND (NOW() + INTERVAL 1 YEAR);

Today

SELECT SYSDATE(); -- 2021-07-17 14:34:09
SELECT NOW(); -- 2021-07-17 14:34:09

From calc from today

SELECT DATE(NOW() – INTERVAL 1 MONTH); — 2021-06-17

Fixed

SELECT DATE(‘2021-07-17’); — 2021-06-17

For example this generates a list of 400 days and selecting only 1 YEAR starting of @s = 2020-01-01 and ends at @e which calculate to 2020-12-31.

SET @s := '2020-01-01';
SET @e := @s + INTERVAL 1 YEAR - INTERVAL 1 DAY; -- set end date to select

SELECT CAST((DATE(@s)+INTERVAL (H+T+U) DAY)AS DATE) d -- generate a list of 400 days starting from @s date so @e can not be more then @s + 
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
 -- generated length of date list can be calculated as fallow 1 * H(3 + 1) * T(9 + 1) * U(9 + 1) = 400
 -- it is crucial to preserve the numbering convention as 1, 2 ... 20, 30..., 100, 200, ... to retrieve chronological date selection
 -- add more UNION 400, 500, 600, ... H(6 + 1) if you want to select from more than 700 days!
WHERE
  (DATE(@s+INTERVAL (H+T+U) DAY)) <= DATE((@e))
ORDER BY d;

If you wish to calculate available range do it as fallow:

SELECT 1 * (3 + 1) * (9 + 1) * (9 + 1); -- date range 400

A much cleaner solution would be to use recursion as fallow

This solution is much cleaner but it is also slower. This might not be the issue but it is nice to take it into consideration (0.037 sec vs 0.001 sec). The larger the date range the slower the query gets. So if you opt for speed you better go with the first solution. The difference is significant.

SET @s := '2021-12-01'; -- start at
SET @e := @s + INTERVAL 1 MONTH - INTERVAL 1 DAY; -- 2021-12-31

WITH recursive d_range AS (
   select @s as Date
   union all
   select Date + interval 1 day
   from d_range
   where Date < @e)
select * from d_range;

Result:

| Date       |
+------------+
| 2021-12-01 |
| 2021-12-02 |
| 2021-12-03 |
| 2021-12-04 |
| 2021-12-05 |
| 2021-12-06 |
| 2021-12-07 |
| 2021-12-08 |
| 2021-12-09 |
| 2021-12-10 |
| 2021-12-11 |
| 2021-12-12 |
| 2021-12-13 |
| 2021-12-14 |
| 2021-12-15 |
| 2021-12-16 |
| 2021-12-17 |
| 2021-12-18 |
| 2021-12-19 |
| 2021-12-20 |
| 2021-12-21 |
| 2021-12-22 |
| 2021-12-23 |
| 2021-12-24 |
| 2021-12-25 |
| 2021-12-26 |
| 2021-12-27 |
| 2021-12-28 |
| 2021-12-29 |
| 2021-12-30 |
| 2021-12-31 |
+------------+
-> 31 rows in set (0.037 sec)