MySQL 如何从mysql中的当前日期时间中减去30天？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10763031/
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
How to subtract 30 days from the current datetime in mysql?
提问by user784637
How do I subtract 30 days from the current datetime in mysql?
如何从 mysql 中的当前日期时间中减去 30 天？
SELECT * FROM table
WHERE exec_datetime BETWEEN DATEDIFF(NOW() - 30 days) AND NOW();
回答by zerkms
SELECT * FROM table
WHERE exec_datetime BETWEEN DATE_SUB(NOW(), INTERVAL 30 DAY) AND NOW();
http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_date-add
http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_date-add
回答by Doug Fir
To anyone who doesn't want to use DATE_SUB, use CURRENT_DATE:
对于不想使用的任何人DATE_SUB，请使用CURRENT_DATE：
SELECT CURRENT_DATE - INTERVAL 30 DAY
回答by Joseph Lust
Let's not use NOW()as you're losing any query caching or optimization because the query is different every time. See the list of functions you should not use in the MySQL documentation.
我们不要使用，NOW()因为您会丢失任何查询缓存或优化，因为每次查询都不同。请参阅MySQL 文档中不应使用的函数列表。
In the code below, let's assume this table is growing with time. New stuff is added and you want to show just the stuff in the last 30 days. This is the most common case.
在下面的代码中，我们假设这个表随着时间的推移而增长。添加了新内容，而您只想显示过去 30 天内的内容。这是最常见的情况。
Note that the date has been added as a string. It is better to add the date in this way, from your calling code, than to use the NOW()function as it kills your caching.
请注意，日期已添加为字符串。最好从调用代码中以这种方式添加日期，而不是使用该NOW()函数，因为它会杀死您的缓存。
SELECT * FROM table WHERE exec_datetime >= DATE_SUB('2012-06-12', INTERVAL 30 DAY);
You can use BETWEENif you really just want stuff from this very second to 30 days before this very second, but that's not a common use case in my experience, so I hope the simplified query can serve you well.
BETWEEN如果您真的只想要从这一秒到这一秒之前 30 天的内容，您可以使用，但根据我的经验，这不是一个常见的用例，所以我希望简化的查询可以很好地为您服务。
回答by Eric Leschinski
MySQL subtract days from now:
MySQL 从现在减去天数：
select now(), now() - interval 1 day
Prints:
印刷：
2014-10-08 09:00:56 2014-10-07 09:00:56
Other Interval Temporal Expression Unit arguments:
其他间隔时间表达式单元参数：
https://dev.mysql.com/doc/refman/5.5/en/expressions.html#temporal-intervals
https://dev.mysql.com/doc/refman/5.5/en/expressions.html#temporal-intervals
select now() - interval 1 microsecond
select now() - interval 1 second
select now() - interval 1 minute
select now() - interval 1 hour
select now() - interval 1 day
select now() - interval 1 week
select now() - interval 1 month
select now() - interval 1 year
回答by Noby Nirmal
You can also use
你也可以使用
select CURDATE()-INTERVAL 30 DAY
回答by Varaj Vignesh
SELECT date_format(current_date - INTERVAL 50 DAY,'%d-%b-%Y')
You can format by using date format in SQL.
您可以使用 SQL 中的日期格式进行格式化。
回答by zzapper
another way
其它的办法
SELECT COUNT(*) FROM tbl_debug WHERE TO_DAYS(`when`) < TO_DAYS(NOW())-30 ;
回答by Pacerier
If you only need the date and not the time use:
如果您只需要日期而不是时间，请使用：
select*from table where exec_datetime
between subdate(curdate(), 30)and curdate();
Since curdate()omits the time component, it's potentiallyfaster than now()and more "semantically correct" in cases where you're only interested in the date.
由于curdate()省略了时间组件，因此在您只对日期感兴趣的情况下，它可能比now()“语义正确”更快，更“语义正确”。
Also, subdate()'s 2-arity overload is potentially faster than using interval.
intervalis meant to be for cases when you need a non-day component.
此外，subdate()2 元重载可能比使用interval.
interval旨在用于您需要非日间组件的情况。
