MySQL 选择从上个月到 (now() - 1 个月) 的所有行，用于比较目的
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6064687/
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 select all rows from last month until (now() - 1 month), for comparative purposes
提问by newyuppie
I need some help writing a MySQL query to show me rows from last month, but not the whole month, only up and until the same day, hour and minute as it is now(), but 1 month before.
我需要一些帮助来编写 MySQL 查询来显示上个月的行，但不是整个月，只显示直到现在（）的同一天、同一小时和同一分钟，但在 1 个月前。
So for example, if today is 5/19 at 5:25pm I need to select rows from midnight 12:00am 4/1 to 5:25pm of 4/19 (from the same year too of course).
例如，如果今天是 5/19 下午 5:25，我需要选择从 4/1 凌晨 12:00 到 4/19 下午 5:25（当然也是同年）的行。
Thanks!
谢谢！
回答by GolezTrol
You can get the first of the month, by calculating the last_day of the month before and add one day. It is awkward, but I think it is better than formatting a date as string and use that for calculation.
您可以通过计算前一个月的 last_day 并添加一天来获得该月的第一天。这很尴尬，但我认为这比将日期格式化为字符串并将其用于计算要好。
select
*
from
yourtable t
where
/* Greater or equal to the start of last month */
t.date >= DATE_ADD(LAST_DAY(DATE_SUB(NOW(), INTERVAL 2 MONTH)), INTERVAL 1 DAY) and
/* Smaller or equal than one month ago */
t.date <= DATE_SUB(NOW(), INTERVAL 1 MONTH)
回答by Ike Walker
Getting one month ago is easy with a single MySQL function:
使用单个 MySQL 函数即可轻松获取一个月前：
SELECT DATE_SUB(NOW(), INTERVAL 1 MONTH);
or
或者
SELECT NOW() - INTERVAL 1 MONTH;
Off the top of my head, I can't think of an elegant way to get the first day of last month in MySQL, but this will certainly work:
在我的头顶上，我想不出一种优雅的方式来获取 MySQL 上个月的第一天，但这肯定会奏效：
SELECT CONCAT(LEFT(NOW() - INTERVAL 1 MONTH,7),'-01');
Put them together and you get a query that solves your problem:
把它们放在一起，你会得到一个可以解决你的问题的查询：
SELECT *
FROM your_table
WHERE t >= CONCAT(LEFT(NOW() - INTERVAL 1 MONTH,7),'-01')
AND t <= NOW() - INTERVAL 1 MONTH
回答by Chinmay235
Simple code please check
简单代码请查收
SELECT * FROM table_name WHERE created <= (NOW() - INTERVAL 1 MONTH)
回答by Senad Me?kin
This is an example of a MySQL date operation relevant to your question:
这是与您的问题相关的 MySQL 日期操作示例：
SELECT DATE_ADD( now( ) , INTERVAL -1 MONTH )
The above will return date time one month ago
以上将返回一个月前的日期时间
So, you can use it, as follows:
因此，您可以使用它，如下所示：
SELECT *
FROM your_table
WHERE Your_Date_Column BETWEEN '2011-01-04'
AND DATE_ADD(NOW( ), INTERVAL -1 MONTH )
回答by unutbu
回答by tilak barthi
SELECT *
FROM
<table_name>
WHERE
<date_field>
BETWEEN
DATE_SUB(NOW(), INTERVAL 1 MONTH) AND NOW();
Similarly, You can select records for 1 month, 2 months etc.
同样，您可以选择 1 个月、2 个月等的记录。
回答by Andomar
You could use a WHEREclause like:
您可以使用如下WHERE子句：
WHERE DateColumn BETWEEN
CAST(date_format(date_sub(NOW(), INTERVAL 1 MONTH),'%Y-%m-01') AS date)
AND
date_sub(now(), INTERVAL 1 MONTH)
回答by Palantir
SELECT *
FROM table
WHERE myDtate BETWEEN now()
, DATE_SUB(NOW()
, INTERVAL 1 MONTH)
回答by Kerry Comeaux
My solution was to avoid using NOW()when writing sql with your programming language, and substitute with a string. The problem with NOW()as you indicate is it includes current time. So to capture from the beginning of the query day (0 hour and minute) instead of:
我的解决方案是在NOW()用你的编程语言编写 sql 时避免使用，并用字符串替换。NOW()正如您所指出的，问题在于它包括当前时间。所以要从查询日的开始（0 小时和分钟）而不是：
r.date <= DATE_SUB(NOW(), INTERVAL 99 DAY)
I did (php):
我做了（php）：
$current_sql_date = date('Y-m-d 00:00:00');
in the sql:
在sql中：
$sql_x = "r.date <= DATE_SUB('$current_sql_date', INTERVAL 99 DAY)"
With that, you will be retrieving data from midnight of the given day
这样，您将从指定日期的午夜开始检索数据
