Select previous 3 months

Question

I am looking to select tickets from the previous 3 months. By calculate the previous 3 months. I saw this post: https://stackoverflow.com/questions/7268896/calculate-3-month-period-using-mysql

But it does not seem to work. Here is my query

select `created`, datediff( now(), `created`) as period , now()
from tag
having period%90=0;

But this is returning dates such as

created             period  now()  
2009-10-25 12:42:15 1170    2013-01-07 10:15:37

any ideas? thanks

having without a group by seems a bit odd. Why not just put the datediff in the where clause instead of defining a period field? — JohnFx, Jan 07 '13 at 16:24
`period % 90 = 0` means periods that are evenly divisible by 90. — Robert Harvey, Jan 07 '13 at 16:25
The original question you linked (and its answer) are a bit misleading; I have closed it. — Robert Harvey, Jan 07 '13 at 16:28

score 1 · Answer 1 · answered Jan 07 '13 at 16:25

1

% is the modulus operator so divides period by 90 and is the remainder tested... ie. 0,90,180,270,... will all return 0.

I think you need

period < 90

for all entries within the last 90 days. If you just want 30 days only period = 90 would do.

answered Jan 07 '13 at 16:25

sradforth

Patrickdev · Answer 2 · 2013-01-07T19:42:08.627

1

You can use DATE_SUB() to get a previous date. In your case, you'll want to use DATE_SUB(NOW(), INTERVAL 3 MONTH).

SELECT FROM tag WHERE DATE_SUB(NOW(), INTERVAL 3 MONTH) < created

Edit: added an alternative for DATE_SUB() as requested below

Alternatively, you can use the following (there might be a much neater way to do this that I currently can't think of):

STR_TO_DATE(CONCAT(DAY(NOW()), '-', MONTH(NOW()) + IF(MONTH(NOW()) > 3, -3, +9), '-', YEAR(NOW()) + IF(MONTH(NOW()) > 3, 0, -1)), '%e-%m-%Y')

edited Jan 07 '13 at 19:42

answered Jan 07 '13 at 16:26

Patrickdev

This seems pretty good select `created`, datediff( now(), `created`) as period , now() FROM tag WHERE DATE_SUB(NOW(), INTERVAL 3 MONTH) < created Let me ask a followup Using "INTERVAL 3 MONTH" October has 31 days, Nov has 30 days, Dec has 31 days. Does the MONTH interval use 30 days always? Does it figure out the proper amount of days? thanks – Greg Jan 07 '13 at 16:30
It does appear that INTERVAL 3 MONTH is really 90 days. mysql does not seem to figure out that Oct has 31, Nov has 30 days, etc... Since my report runs on the first of the month, every month, would there be a way to just generate - 3 months back? So if the report ran on 1-1-2013 to manually figure out 10-1-2012. instead of INTERVAL 3 MONTH - really 90 days. giving 10-3-2012 to 1-1-2013 – Greg Jan 07 '13 at 16:59
I suppose that instead of using the `DATE_SUB()` function, you could also create the date as a string, and then let MySQL parse it. I've added an example in my post above. – Patrickdev Jan 07 '13 at 19:41

2 Answers2