mySQL - limit on datetime

Question

I have a mySQL table containing matchs and their dates (in a DateTime column).

Is it possible to get all matches from day X to X+n, and then all matches from day X+n to day X+2n...?

I would use that to "limit" my results: If I'm on page 1, I get all matches from day 1 to day 5 (for example), on page 2, I get all matches from day 6 to day 10 etc.

I guess I can't use the LIMIT clause because it only limits rows I get (based on a number of rows I want and not on a number of days)

Answer 1

you can do it with the DATE_ADD function

SELECT * 
FROM table 
WHERE date BETWEEN CURDATE() AND DATE_ADD(CURDATE(), INTERVAL 30 DAY)

http://dev.mysql.com/doc/refman/5.6/en/date-and-time-functions.html#function_date-add

Edit:

If you want to use it for pagination you can do it with the following:

$page = 0; //receive it i.e with $_GET, but be aware of sql injection
$intervalInDays = 30;
//create db connection...
$sql = "
SELECT * 
FROM table 
WHERE date 
    BETWEEN DATE_ADD(DATE('your startdate'), INTERVAL (".$page * $interval.") DAY) 
    AND DATE_ADD(DATE('your startdate'), INTERVAL (".($page+1) * $interval.") DAY)";
//query and fetch...