How to SELECT and move forward by 7 DAYS MYSQL

Question

Can somebody please explain how I would SELECT a DATE and move forward in intervals of 7 DAYS given a date range in a WHERE clause?

Here's my example SQL:

SELECT DATE(*TIMESTAMP*) + (DATE_ADD(DATE(*TIMESTAMP*), INTERVAL 7 DAY)) as `interval`
WHERE digital_lead.received <= '2014-12-16 08:37:00' AND
digital_lead.received >= '2014-11-14 08:37:00'

So the expected output would appear like

interval 
2014-11-14
2014-11-21
2014-11-28
2014-12-05

The SELECT statement moves forward 7 DAYS given the date range in the WHERE clause.

Any suggestions?

score 1 · Accepted Answer · edited May 23 '17 at 12:05

With the help of this answer: https://stackoverflow.com/a/2652051/2307520 from similar question, I were able to do this:

select date_add('2014-11-14 08:37:00', INTERVAL (row) DAY) FROM
(
SELECT @row := @row + 7 as row FROM 
(select 0 union all select 1 union all select 3 union all select 4 union all select 5 union all select 6 union all select 6 union all select 7 union all select 8 union all select 9) t,
(select 0 union all select 1 union all select 3 union all select 4 union all select 5 union all select 6 union all select 6 union all select 7 union all select 8 union all select 9) t2, 
(select 0 union all select 1 union all select 3 union all select 4 union all select 5 union all select 6 union all select 6 union all select 7 union all select 8 union all select 9) t3, 
(select 0 union all select 1 union all select 3 union all select 4 union all select 5 union all select 6 union all select 6 union all select 7 union all select 8 union all select 9) t4, 
(SELECT @row:=-7) q
WHERE @row<=DATEDIFF('2014-12-16 08:37:00' , '2014-11-14 08:37:00')
) z

How would I include the start date in the results? As this begins from the 21st? Thanks — aphextwix, Dec 05 '14 at 12:08
updated my answer ; just modify these two (three actually, start date is shown twince) timestamps. — David162795, Dec 05 '14 at 12:09

How to SELECT and move forward by 7 DAYS MYSQL

1 Answers1