Mysql counting rows in a period of time

Question

Lets say I have a user table which looks like

userID int(4) unsigned not null auto_increment,
name varchar(50) not null,
date_start int(10) unsigned not null,
date_stop int(10) unsigned not null

I want to count all users that were active (between date_start and date_stop, these are unix_timestamps()) per month.

So the result should be something like this :

2012/01    55
2012/02    58
2012/03    51

I'd say something like this, but obviously I'm missing something :

SELECT 
    DATE_FORMAT(???, '%Y/%m'), 
    COUNT(userID) 
FROM users 
WHERE 
    ??? BETWEEN date_start AND date_stop 
GROUP BY DATE_FORMAT(???, '%Y%m');

... just to clarify, what worked for me was :

SELECT m.yearMonth, COUNT(u.userID) 
FROM users u 
LEFT JOIN months m ON m.yearMonth BETWEEN DATE_FORMAT(FROM_UNIXTIME(u.date_start), '%Y%m') AND DATE_FORMAT(FROM_UNIXTIME(u.date_stop), '%Y%m') 
GROUP BY m.yearMonth;

Using a table that has "all" year/months stored in the form of yyyymm.

Answer 1

Firstly, you should have a table that contains the year, month details in yyyyMM format. Then you would need to join that table with the users table with yyyyMM portion of date_start less than the value in the months table and date_end greater than or equal to the value in the months table.

This would count a user who was active for more than one month, once per each month, which I think is what you expect.

CREATE TABLE months(yearMonth INT);
INSERT INTO months VALUES(201201);
INSERT INTO months VALUES(201202);
INSERT INTO months VALUES(201203);
....

SELECT m.yearMonth, COUNT(*) 
FROM 
    months m, users u
WHERE
    m.yearMonth >= CONVERT(INT, 
                        CONVERT(VARCHAR(4), DATEPART(yy, date_start) + 
                        CONVERT(VARCHAR(2), DATEPART(mm, date_start)
                      )
AND m.yearMonth <= CONVERT(INT, 
                        CONVERT(VARCHAR(4), DATEPART(yy, date_end) + 
                        CONVERT(VARCHAR(2), DATEPART(mm, date_end)
                      )

Note: This is SQL Server but you should be able to get the MySQL counterpart for CONVERT/DATEPART functions, I guess.