MySQL select all rows from last month until (now() - 1 month), for comparative purposes

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.

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).

Thanks!

219984 次浏览
小开 
SELECT *
FROM table
WHERE date BETWEEN
ADDDATE(LAST_DAY(DATE_SUB(NOW(),INTERVAL 2 MONTH)), INTERVAL 1 DAY)
AND DATE_SUB(NOW(),INTERVAL 1 MONTH);

See the docs for info on DATE_SUB, ADDDATE, LAST_DAY and other useful datetime functions.

小开

This is an example of a MySQL date operation relevant to your question:

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 )
小开

You could use a WHERE clause like:

WHERE DateColumn BETWEEN
CAST(date_format(date_sub(NOW(), INTERVAL 1 MONTH),'%Y-%m-01') AS date)
AND
date_sub(now(), INTERVAL 1 MONTH)
小开 
SELECT *
FROM table
WHERE myDtate BETWEEN now()
, DATE_SUB(NOW()
, INTERVAL 1 MONTH)
小开

Getting one month ago is easy with a single MySQL function:

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:

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
小开
最佳答案

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.

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)
小开

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:

r.date <= DATE_SUB(NOW(), INTERVAL 99 DAY)

I did (php):

$current_sql_date = date('Y-m-d 00:00:00');

in the 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

小开

Simple code please check

SELECT * FROM table_name WHERE created >= (NOW() - INTERVAL 1 MONTH)
小开 
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.

小开

maybe like this - all last month DATE(recDate) BETWEEN DATE_ADD(LAST_DAY(DATE_SUB(CURDATE(), INTERVAL 2 MONTH)), INTERVAL 1 DAY) AND LAST_DAY(DATE_SUB(CURDATE(), INTERVAL 1 MONTH) )


提交答案