Reputation: 1858
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?
Upvotes: 0
Views: 131
Reputation: 1866
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
Upvotes: 1