MySQL Query to group by date range?

Question

I've got a table in MySQL that shows me number of hours logged on a daily basis. I'm trying to build a view that will allow me to quickly group my data by blocks/range of days. The simplest case would be on a monthly basis which wouldn't be difficult. I could just select the date as "%y-%m" and then group by that column.

Ex:

select time_logged, date_format(start_date, '%Y-%m') AS `month_logged`
from work_log
group by month_logged

That works fine if I am just grouping by month. But my issue is that I need to group from the 13th of the month to the 12th of the following month (ex: July 13-Aug 12, Aug 13- Sept 12, etc).

Is there an easy way to do something like that in a single query/view? I can't seem to come up with a query that works for my needs, even playing with the different date field combinations.

Solution

Subtract 13 days and do the grouping you are doing now:

select time_logged,
       date_format(start_date - interval 12 day, '%Y-%m') AS `month_logged`
from work_log
group by month_logged;

Comments

[Joachim Isaksson]

12 days I think, but +1 :)

[Eric B.]

Smack. Of course. That would definitely be easiest. I finally got it working with a complicated if clause, but this is so much more straightforward and obvious! Thanks!