昨天的所有 MySQL 记录
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/34913328/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
All MySQL records from yesterday
提问by eComEvo
What is the most efficient way to get all records with a datetime field that falls somewhere between yesterday at 00:00:00and yesterday at 23:59:59?
获取日期时间字段介于昨天00:00:00和昨天之间的所有记录的最有效方法是什么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.
假设昨天是 2016-01-19，那么在这种情况下，我只想返回第 1 行和第 2 行。
回答by miken32
Since you're only looking for the date portion, you can compare those easily using MySQL's DATE()function.
由于您只是在寻找日期部分，因此您可以使用MySQL 的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:
请注意，如果您有大量记录，这可能效率低下；的派生值失去了索引优势DATE()。在这种情况下，您可以使用以下查询：
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.
这是有效的，因为CURDATE()假设返回的日期值具有 00:00:00 的时间戳。索引仍然可以使用，因为日期列的值根本没有被转换。
回答by Alan Hadsell
You can still use the index if you say
如果你说，你仍然可以使用索引
SELECT * FROM TABLE
WHERE CREATED_AT >= CURDATE() - INTERVAL 1 DAY
AND CREATED_AT < CURDATE();
回答by Wilnar Vincent
Hereis the same question with an answer. To summarize answer for you, use subdate()as suggested by Sajmon.
这是相同的问题和答案。要为您总结答案，请按照 Sajmon 的建议使用subdate()。
subdate(currentDate, 1)
using your table it should be.
使用你的桌子应该是。
select *
from tablename
where created_at between subdate(CURDATE(), 1)
and date (now() )
回答by pivanchy
use:
subdate(current_date, 1)
用：
subdate(current_date, 1)
it's awesome for your case!
你的情况太棒了！
回答by Anthony
You can use subdateto indicate "yesterday" and use date()to indicate that you want records where just the date part of the column matches. So:
您可以使用subdate来指示“昨天”并使用date()来指示您想要仅与列的日期部分匹配的记录。所以：
SELECT *
FROM tablename
WHERE DATE(created_at) = SUBDATE(CURRENT_DATE(), INTERVAL 1 DAY)
