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

Question

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!

Answer 1

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) 

Answer 2

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