查询以获取上个月的所有行

我需要选择数据库中上个月创建的所有行。

例如，如果当前月份是1月份，那么我想返回在12月份创建的所有行，如果月份是2月份，那么我想返回在1月份创建的所有行。我的数据库中有一个 date_created列，列出了以这种格式创建的日期: 2007-06-05 14:50:17。

188899 次浏览
小开
最佳答案 
SELECT * FROM table
WHERE YEAR(date_created) = YEAR(CURRENT_DATE - INTERVAL 1 MONTH)
AND MONTH(date_created) = MONTH(CURRENT_DATE - INTERVAL 1 MONTH)
小开

select fields FROM table WHERE date_created LIKE concat(LEFT(DATE_SUB(NOW(), interval 1 month),7),'%');

this one will be able to take advantage of an index if your date_created is indexed, because it doesn't apply any transformation function to the field value.

小开

Here's another alternative. Assuming you have an indexed DATE or DATETIME type field, this should use the index as the formatted dates will be type converted before the index is used. You should then see a range query rather than an index query when viewed with EXPLAIN.

SELECT
*
FROM
table
WHERE
date_created >= DATE_FORMAT( CURRENT_DATE - INTERVAL 1 MONTH, '%Y/%m/01' )
AND
date_created < DATE_FORMAT( CURRENT_DATE, '%Y/%m/01' )
小开

If there are no future dates ...

SELECT *
FROM   table_name
WHERE  date_created > (NOW() - INTERVAL 1 MONTH);

Tested.

小开

Alternatively to hobodave's answer

SELECT * FROM table
WHERE YEAR(date_created) = YEAR(CURRENT_DATE - INTERVAL 1 MONTH)
AND MONTH(date_created) = MONTH(CURRENT_DATE - INTERVAL 1 MONTH)

You could achieve the same with EXTRACT, using YEAR_MONTH as unit, thus you wouldn't need the AND, like so:

SELECT * FROM table
WHERE EXTRACT(YEAR_MONTH FROM date_created) = EXTRACT(YEAR_MONTH FROM CURDATE() - INTERVAL
1 MONTH)
小开

Even though the answer for this question has been selected already, however, I believe the simplest query will be

SELECT *
FROM table
WHERE
date_created BETWEEN (CURRENT_DATE() - INTERVAL 1 MONTH) AND CURRENT_DATE();
小开

Here is the query to get the records of the last month:

SELECT *
FROM `tablename`
WHERE `datefiled`
BETWEEN DATE_SUB( DATE( NOW( ) ) , INTERVAL 1
MONTH )
AND
LAST_DAY( DATE_SUB( DATE( NOW( ) ) , INTERVAL 1
MONTH ) )

Regards - saqib

小开 
WHERE created_date >= DATE_ADD(LAST_DAY(DATE_SUB(NOW(), INTERVAL 2 MONTH)), INTERVAL 1 DAY)
AND created_date <= DATE_ADD(LAST_DAY(DATE_SUB(NOW(), INTERVAL 1 MONTH)), INTERVAL 0 DAY)

This worked for me (Selects all records created from last month, regardless of the day you run the query this month)

小开 
SELECT *
FROM  yourtable
where DATE_FORMAT(date_created, '%Y-%m') = date_format(DATE_SUB(curdate(), INTERVAL 1 month),'%Y-%m')

This should return all the records from the previous calendar month, as opposed to the records for the last 30 or 31 days.

小开 
SELECT *  FROM table
WHERE  YEAR(date_created) = YEAR(CURRENT_DATE - INTERVAL 1 MONTH)
AND MONTH(date_created) = MONTH(CURRENT_DATE - INTERVAL 1 MONTH)
小开

Alternative with single condition

SELECT * FROM table
WHERE YEAR(date_created) * 12 + MONTH(date_created)
= YEAR(CURRENT_DATE) * 12 + MONTH(CURRENT_DATE) - 1
小开

One more way to do this in:

MYSQL

select * from <table_name> where date_created >= DATE_ADD(NOW(), INTERVAL -30 DAY);
小开

if you want to get orders from last month, you can try using

WHERE MONTH(order_date) = MONTH(CURRENT_DATE()) -1

提交答案