sql - mysql group by month with custom starting day -

is there way in mysql group month, custom starting dates.

say want count logins in monthly basis, condition month starts when user register.

so example user a registered on january 30th , user b on january 15th

i should group logins follow:

* user a: january 30th - february 28th, march 1st - march 30th, march 31 - april 30 , on , forth  * user b: january 15th - february 14th, february 15th - march 14th , on , forth 

i guess need use date_add('2013-01-30', interval 1 month); can not seem find way make grouping.

update

@garethd: right typo

in general month should start @ same day of next month or last day of next month in case first not possible, if registered in day 31, month period start in day 30 months not have 31 days , last day of february either 28 or 29

example:

given that

id 1 registered on 2012-12-16 id 2 registered on 2013-01-29 

and following table

+----+------------+ | id |    date    | +----+------------+ |  1 | 2013-01-15 | |  1 | 2013-01-16 | |  1 | 2013-01-17 | |  1 | 2013-01-17 | |  2 | 2013-03-20 | |  2 | 2013-03-21 | |  2 | 2013-03-28 | |  2 | 2013-03-29 | |  2 | 2013-03-30 | +----+------------+ 

the results should be

+----+----------------------------+-------+ | id |           range            | count | +----+----------------------------+-------+ | 1  | 2012-12-16, 2013-01-15     |     1 | | 1  | 2013-01-16, 2013-02-15     |     3 | | 2  | 2013-02-2[8|9], 2013-03-28 |     3 | | 2  | 2013-03-29, 2013-04-28     |     2 | +----+----------------------------+-------+ 

i hope intent clearer now.

for following assuming have numbers table, if don't have numbers table, i'd recommend make 1 then, if don't want can create number list on fly

you can list of boundaries cross joining userid , registered dates numbers table:

select  u.id,          date_add(registereddate, interval n.number month) periodstart,         date_add(registereddate, interval n.number + 1 month) periodend    user u         cross join numbers n; 

this gives table like:

id  periodstart     periodend 1   2012-12-16      2012-12-16 2   2013-01-29      2013-01-29 1   2013-01-16      2013-01-16 2   2013-02-28      2013-02-28 

example on sql fiddle

you need join main table, , count:

select  u.id,         u.periodstart,         date_add(periodend, interval -1 day) periodend,         count(*) `count`    (   select  u.id,                      date_add(registereddate, interval n.number month) periodstart,                     date_add(registereddate, interval n.number + 1 month) periodend                user u                     cross join numbers n         ) u         inner join t             on t.id = u.id             , t.date >= u.periodstart             , t.date < periodend group u.id, u.periodstart, u.periodend; 

giving final result of:

id  periodstart     periodend   count 1   2012-12-16      2013-01-15  1 1   2013-01-16      2013-02-15  3 2   2013-02-28      2013-03-28  3 2   2013-03-29      2013-04-28  2 

full example on sql-fiddle

you can concatenate period start , end dates make 'range' string, best handled in application layer.

edit

this can achieved no subqueries perform better:

select  u.id,         date_add(u.registereddate, interval n.number month) periodstart,         date_add(date_add(u.registereddate, interval n.number + 1 month), interval -1 day) periodend,         count(*) `count`    user u         cross join numbers n          inner join t             on t.id = u.id             , t.date >= date_add(u.registereddate, interval n.number month)             , t.date < date_add(u.registereddate, interval n.number + 1 month) group u.id, u.registereddate, n.number; 

example no subquery on sql-fiddle

edit 2

this periods users until current period (i.e. today falls within date range)

select  u.id,         date_add(u.registereddate, interval n.number month) periodstart,         date_add(date_add(u.registereddate, interval n.number + 1 month), interval -1 day) periodend,         count(t.id) `count`    user u         cross join numbers n          left join t             on t.id = u.id             , t.date >= date_add(u.registereddate, interval n.number month)             , t.date < date_add(u.registereddate, interval n.number + 1 month)   date_add(u.registereddate, interval n.number + 1 month) <= current_timestamp group u.id, u.registereddate, n.number; 

example on sql fiddle