MySQL – How to Get All Data Before and After 10 Days Interval

database-designMySQLmysql-5.6

    SELECT * FROM events  WHERE `date` 
    BETWEEN NOW() AND DATE_ADD
    (NOW(), INTERVAL 10 DAY) order by date

I am using the above code to extract the events data of ten days of interval. Now I want to Extract out all the data before this 10 days of interval and after 10 days of interval. How can I do that in a perfect way?

Please help. Advance thanks for your time

Best Answer

It is probably best to issue two queries.

SELECT * FROM events
    WHERE `date` >= NOW() - INTERVAL 10 DAY
      AND `date`  < NOW();

SELECT * FROM events
    WHERE `date` >= NOW()
      AND `date`  < NOW() + INTERVAL 10 DAY;

You could get both at once, but it could be inconvenient if you need to separate them:

SELECT * FROM events
    WHERE `date` >= NOW() - INTERVAL 10 DAY
      AND `date`  < NOW() + INTERVAL 10 DAY;

If you want to base on midnight instead of the current second, use CURDATE() instead of NOW().

Related Question