MySQL 查询 - 今天和过去 30 天之间的记录

2021-11-20 00:00:00 date select mysql

我想返回过去 30 天内添加到数据库的所有记录.由于显示目的，我需要将日期转换为 mm/dd/yy.

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. 

create_date between DATE_FORMAT(curdate(),'%m/%d/%Y') AND (DATE_FORMAT(curdate() - interval 30 day,'%m/%d/%Y'))

我的语句未能将记录限制为过去 30 天 - 它选择了所有记录.

My statement fails to limit the records to the last 30 days - it selects all the records.

谁能指出我正确的方向?感觉就像我很接近.

Can anyone point me in the right direction? It feels like I am close.

谢谢，祝您一周愉快.

推荐答案

您需要在 SELECT 子句中应用 DATE_FORMAT，而不是 WHERE> 条款:

You need to apply DATE_FORMAT in the SELECT clause, not the WHERE clause:

SELECT  DATE_FORMAT(create_date, '%m/%d/%Y')
FROM    mytable
WHERE   create_date BETWEEN CURDATE() - INTERVAL 30 DAY AND CURDATE()

另请注意，CURDATE() 仅返回日期的 DATE 部分，因此如果您将 create_date 存储为 DATETIME 填充时间部分，此查询将不会选择今天的记录.

Also note that CURDATE() returns only the DATE portion of the date, so if you store create_date as a DATETIME with the time portion filled, this query will not select the today's records.

在这种情况下，您需要使用 NOW 代替:

In this case, you'll need to use NOW instead:

SELECT  DATE_FORMAT(create_date, '%m/%d/%Y')
FROM    mytable
WHERE   create_date BETWEEN NOW() - INTERVAL 30 DAY AND NOW()

相关文章