使用日期时间和 php 从 1、7 和 30 天前选择 mysql db 中的记录
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3283358/
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
selecting records in mysql db from 1, 7, and 30 days ago with datetime and php
提问by mrpatg
Im using the following query to target results that are exactly X days older than current time.
我使用以下查询来定位比当前时间早 X 天的结果。
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
问题是如果不存在恰好 30 天前的记录以及不相关的数据，则它会从当天返回数据
is there a better way of doing this?
有没有更好的方法来做到这一点？
回答by Bill Karwin
BETWEENincludes all values in between the two arguments, including the value at each end. In other words, BETWEEN 1 AND 4includes values 1, 2, 3, and 4. Not just 1 and 4, and not just 2 and 3.
BETWEEN包括两个参数之间的所有值，包括每一端的值。换句话说，BETWEEN 1 AND 4包括值 1、2、3 和 4。不仅仅是 1 和 4，也不仅仅是 2 和 3。
If you just want dates from the single day that is 30 days ago, try this:
如果您只想要 30 天前的某一天的日期，请尝试以下操作：
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.
使用CURDATE()而不是SYSDATE()因为CURDATE()返回没有时间组件的日期。
回答by OMG Ponies
Your query is set to obtain records between today (including time) and 30 days previous.
您的查询设置为获取今天（包括时间）和前 30 天之间的记录。
If you want records that are older than 30 days (to the time), use:
如果您想要超过 30 天（到时间）的记录，请使用：
SELECT *,
DATE_FORMAT(datetime, '%m/%d/%Y')
FROM table
WHERE datetime <= DATE_SUB(SYSDATE(), INTERVAL 30 DAY)
ORDER BY ID DESC
If you want those that are only 30 days old, not 31 or 29, without respect for the time portion - use:
如果你想要那些只有 30 天，而不是 31 或 29 天的那些，而不考虑时间部分 - 使用：
SELECT *,
DATE_FORMAT(datetime, '%m/%d/%Y')
FROM table
WHERE DATE_FORMAT(datetime, '%m/%d/%Y') = DATE_FORMAT(DATE_SUB(SYSDATE(), INTERVAL 30 DAY), '%m/%d/%Y')
ORDER BY ID DESC
