MySQL how to split a date into known intervals and transform it in rows?

Question

How do I split a selected date value into many rows, with as a known interval until now for example? I could do it using Recursive CTE, but MySql Aurora(RDS) does't give support.

eg: taking a single result like

|--|----------|
|id|createdAt |
|--|----------|
|1 |2018-01-01|
|--|----------|

Given a 7 days interval, the query should output data like:

|----------|
|clycles   |
|----------|
|2018-01-01|
|2018-01-08|
|2018-01-15|
|2018-01-22|
|...       |
|----------|

Answer 1

I would use temporary table for this

DELIMITER //
CREATE PROCEDURE `increment_date_till_now`(
    IN `date_start` DATE,
    IN `day_interval` INT
)
BEGIN
  CREATE TEMPORARY TABLE `__increment_days` (`date` DATE NOT NULL);
  SET @dt = date_start;
  INSERT INTO `__increment_days` VALUES (@dt);
  WHILE (@dt := @dt + INTERVAL day_interval DAY) < CURRENT_DATE() DO
    INSERT INTO `__increment_days` VALUES (@dt);
  END WHILE;
  SELECT * FROM `__increment_days`;
  DROP TABLE `__increment_days`;
END//

And then

CALL increment_date_till_now('2018-01-01', 7);

Answer 2

If you have a (possibly temporary) table of all possible dates in a column d, you can

SELECT whatever, d FROM somewhere 
JOIN dates 
WHERE d BETWEEN createdAt AND CURRENT_DATE() 
AND MOD(DATEDIFF(d, createdAt),7)=0

See also How to populate a table with a range of dates?