MySQL year interval that Includes the entire month from the previous year

Question

I have MySQL condition that grabs a time interval from now back x number of months. Typically, this will be set to 13 months so you can compare the current month to that of last year.

'created > DATE_SUB(now(), INTERVAL ' . $timeInterval . ' MONTH)'

So for example last January compared to this January, but I'd like to include all of the previous years month. So instead of January 20, 2015 to January 20, 2016 I would have January 01, 2015 to the current date in January this year until February 1st.

Answer 1

I'd use DATE_FORMAT to make it quick and easy, replace the "day" part of the date with a constant. Then subtract your number of months...

... t.created > DATE_FORMAT(NOW(),'%Y-%m-01') - INTERVAL ? MONTH

---

As a demonstration of what is returned by that expression, we can test it using a simple SELECT statement:

SELECT NOW(), DATE_FORMAT(NOW(),'%Y-%m-01') - INTERVAL 12 MONTH

NOW()                DATE_FORMAT(NOW(),'%Y-%m-01') - INTERVAL 12 MONTH  
-------------------  -------------------------------------------------
2016-01-27 21:01:02  2015-01-01   

---

FOLLOWUP

Are you sure you want a "greater than" comparison, rather than a "greater than or equal to" comparison >= ?

There are other approaches to generating that date value to compare to. You could use DATE(NOW()) or CURDATE() to return the current date with no time component.

And use the DAY() function to get the numeric value of the current day, and then subtract that (minus 1) as a number of days. For example, something like this:

 >= DATE(NOW()) - INTERVAL DAY(NOW())-1 DAY - INTERVAL 12 MONTH

That seems messier and more complicated. I think it's easier to understand stuffing in the '-01' as the day part.

Answer 2

created > str_to_date(concat(year(now())-1, '-01-01'), '%Y-%m-%d')

Or if you need not all previous year:

select str_to_date(concat(year(now())-1, '-', month(now()),'-01'), '%Y-%m-%d')