Brandin Arsenault Brandin Arsenault - 1 month ago 6
MySQL Question

MySQL Interval Issue Extra Results

I manged to get my query together to select the past 7 days, and I modified it so it is supposed to select the past 7 days from the date I enter instead of

NOW()
, however when I do this, it is producing results 7 days prior to the date, and then every entry AFTER the date:

date Ascending
2016-10-02
2016-10-03
2016-10-05
2016-10-06
2016-10-07
2016-10-17
2016-10-18
2016-10-20
2016-10-21
2016-10-22
2016-10-23


When it should be exporting:

date Ascending
2016-10-02
2016-10-03
2016-10-05
2016-10-06
2016-10-07


The query:

SELECT DATE
FROM `dates`
WHERE DATE > ( '2016-10-08' - INTERVAL 1 WEEK )
ORDER BY `date`


I appreciate your assistance, I simply cannot figure this out.

Answer

Then add an extra criteria to the where clause to cap the returned records at the date specified:

WHERE DATE > (  '2016-10-08' - INTERVAL 1 WEEK ) AND DATE <= '2016-10-08' 

Any date after the specified date will be greater than 1 week before the specified date, therefore you need to have the additional criterion.