selecting records in mysql db from 1, 7, and 30 days ago with datetime and php

Question

Im using the following query to target results that are exactly X days older than current time.

SELECT  *,
        DATE_FORMAT(datetime, '%m/%d/%Y') 
   FROM table 
  WHERE datetime BETWEEN SYSDATE() - INTERVAL 30 DAY 
                     AND SYSDATE() 
ORDER BY ID DESC

Problem is its returning data from current day if a record from exactly 30 days ago doesnt exist, as well as irrelevant data

is there a better way of doing this?

Answer 1

Your query is set to obtain records between today (including time) and 30 days previous.

If you want records that are older than 30 days (to the time), use:

  SELECT *,
         DATE_FORMAT(datetime, '%m/%d/%Y') 
    FROM table 
   WHERE datetime <= DATE_SUB(SYSDATE(), INTERVAL 30 DAY)
ORDER BY ID DESC

If you want those that are only 30 days old, not 31 or 29, without respect for the time portion - use:

  SELECT *,
         DATE_FORMAT(datetime, '%m/%d/%Y') 
    FROM table 
   WHERE DATE_FORMAT(datetime, '%m/%d/%Y') = DATE_FORMAT(DATE_SUB(SYSDATE(), INTERVAL 30 DAY), '%m/%d/%Y') 
ORDER BY ID DESC

Answer 2

BETWEEN includes all values in between the two arguments, including the value at each end. In other words, BETWEEN 1 AND 4 includes values 1, 2, 3, and 4. Not just 1 and 4, and not just 2 and 3.

If you just want dates from the single day that is 30 days ago, try this:

SELECT  *,
        DATE_FORMAT(datetime, '%m/%d/%Y') 
   FROM table 
  WHERE DATE(datetime) = CURDATE() - INTERVAL 30 DAY 
ORDER BY ID DESC

Use CURDATE() instead of SYSDATE() because CURDATE() returns a date without a time component.