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

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.