All MySQL records from yesterday

Since you're only looking for the date portion, you can compare those easily using MySQL's DATE() function.

SELECT * FROM table WHERE DATE(created_at) = DATE(NOW() - INTERVAL 1 DAY);

Note that if you have a very large number of records this can be inefficient; indexing advantages are lost with the derived value of DATE(). In that case, you can use this query:

SELECT * FROM table
    WHERE created_at BETWEEN CURDATE() - INTERVAL 1 DAY
        AND CURDATE() - INTERVAL 1 SECOND;

This works because date values such as the one returned by CURDATE() are assumed to have a timestamp of 00:00:00. The index can still be used because the date column's value is not being transformed at all.

You can still use the index if you say

SELECT * FROM TABLE
WHERE CREATED_AT >= CURDATE() - INTERVAL 1 DAY
  AND CREATED_AT < CURDATE();

You can use subdate to indicate "yesterday" and use date() to indicate that you want records where just the date part of the column matches. So:

SELECT *
FROM tablename
WHERE DATE(created_at) = SUBDATE(CURRENT_DATE(), INTERVAL 1 DAY)

All MySQL records from yesterday

Related

Recent Posts