All MySQL records from yesterday

Question

What is the most efficient way to get all records with a datetime field that falls somewhere between yesterday at 00:00:00 and yesterday at 23:59:59?

Table:

id    created_at 1     2016-01-19 20:03:00 2     2016-01-19 11:12:05 3     2016-01-20 03:04:01 

Suppose yesterday was 2016-01-19, then in this case all I'd want to return is rows 1 and 2.

Answer 1

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.

Answer 2

You can still use the index if you say

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