MySQL 查询 - 今天和过去 30 天之间的记录
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2041575/
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
MySQL Query - Records between Today and Last 30 Days
提问by Jason Sweet
I want to return all records that were added to the database within the last 30 days. I need to convert the date to mm/dd/yy because of display purposes.
我想返回过去 30 天内添加到数据库的所有记录。由于显示目的，我需要将日期转换为 mm/dd/yy。
create_date between DATE_FORMAT(curdate(),'%m/%d/%Y') AND (DATE_FORMAT(curdate() - interval 30 day,'%m/%d/%Y'))
My statement fails to limit the records to the last 30 days - it selects all the records.
我的语句未能将记录限制为过去 30 天 - 它选择了所有记录。
Can anyone point me in the right direction? It feels like I am close.
任何人都可以指出我正确的方向吗？感觉就像我很接近。
Thanks and have a great week.
谢谢，祝你一周愉快。
回答by Quassnoi
You need to apply DATE_FORMATin the SELECTclause, not the WHEREclause:
您需要DATE_FORMAT在SELECT子句中应用，而不是在WHERE子句中：
SELECT DATE_FORMAT(create_date, '%m/%d/%Y')
FROM mytable
WHERE create_date BETWEEN CURDATE() - INTERVAL 30 DAY AND CURDATE()
Also note that CURDATE()returns only the DATEportion of the date, so if you store create_dateas a DATETIMEwith the time portion filled, this query will not select the today's records.
另请注意，CURDATE()仅返回DATE日期的部分，因此如果您将时间部分存储create_date为DATETIME填充的时间部分，则此查询将不会选择今天的记录。
In this case, you'll need to use NOWinstead:
在这种情况下，您需要NOW改用：
SELECT DATE_FORMAT(create_date, '%m/%d/%Y')
FROM mytable
WHERE create_date BETWEEN NOW() - INTERVAL 30 DAY AND NOW()
回答by Thinkcast
SELECT
*
FROM
< table_name >
WHERE
< date_field > BETWEEN DATE_SUB(NOW(), INTERVAL 30 DAY)
AND NOW();
回答by Rich Adams
DATE_FORMATreturns a string, so you're using two strings in your BETWEENclause, which isn't going to work as you expect.
DATE_FORMAT返回一个字符串，因此您在BETWEEN子句中使用了两个字符串，这不会像您预期的那样工作。
Instead, convert the date to your format in the SELECTand do the BETWEENfor the actual dates. For example,
相反，日期转换到你的格式的SELECT，做BETWEEN的实际日期。例如，
SELECT DATE_FORMAT(create_date, '%m/%d/%y') as create_date_formatted
FROM table
WHERE create_date BETWEEN (CURDATE() - INTERVAL 30 DAY) AND CURDATE()
回答by Rohit Suthar
You can also write this in mysql -
你也可以在mysql中写这个——
SELECT DATE_FORMAT(create_date, '%m/%d/%Y')
FROM mytable
WHERE create_date < DATE_ADD(NOW(), INTERVAL -1 MONTH);
FIXED
固定的
回答by onlybymyexerperience
For the current date activity and complete activity for previous 30 days use this, since the SYSDATE is variable in a day the previous 30th day will not have the whole data for that day.
对于当前日期活动和前 30 天的完整活动，请使用此选项，因为 SYSDATE 在一天中是可变的，因此前 30 天不会有当天的全部数据。
SELECT DATE_FORMAT(create_date, '%m/%d/%Y')
FROM mytable
WHERE create_date BETWEEN CURDATE() - INTERVAL 30 DAY AND SYSDATE()
回答by mvirant
Here's a solution without using curdate()function, this is a solution for those who use TSQLI guess
这是一个不使用curdate()函数的解决方案，这是TSQL我猜那些使用的解决方案
SELECT myDate
FROM myTable
WHERE myDate BETWEEN DATEADD(DAY, -30, GETDATE()) AND GETDATE()
