All MySQL records for yesterday

Question

What is the most effective way to get all records with the field datetime, which is somewhere between yesterday 00:00:00and yesterday 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 would like to return is lines 1 and 2.

Answer 1

Since you are only looking for a part of the date, you can easily compare it using the MySQL DATE()function . Please note that if you have a very large number of entries, this may be ineffective; the benefits of indexing are lost with the derived value DATE().

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

Answer 2

,

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

Answer 3

:   subdate(current_date, 1)

!

Answer 4

. , subdate(), .

subdate(currentDate, 1)

, .

select *
from tablename
where created_at between subdate(CURDATE(), 1)
and date (now() )

Answer 5

subdate, "", date(), , , . :

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