CODEWITHSUNDEEP
mysql
aphextwix
aphextwix

Reputation: 1858

How to SELECT and move forward by 7 DAYS MYSQL

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

Answers (1)

David162795
David162795

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

Related Questions