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.

score 1 · Accepted Answer · answered Dec 20 '12 at 11:37

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.

My first guess is that this is the correct solution to my problem. Going to work and I'll have feedback asap. — b00st, Dec 20 '12 at 12:58
This one did the job, including the months-table, although syntax is different in MySQL. I did a LEFT JOIN because I only want results where at least one user was active. I came up with this : 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; — b00st, Dec 20 '12 at 14:08

echo_Me · Answer 2 · 2012-12-20T12:06:49.413

0

try something like that

    SELECT DATE_FORMAT(???, '%Y/%m') , userID 
     FROM users 
     WHERE ??? BETWEEN '2012/01' AND '2012/05' 
     GROUP BY DATE_FORMAT(???, '%Y%m');

u was right just dont use count(userID)

EDIT:

you are missing one column in your database . it should be last_active_date so when user login it will update this date of last_active_date and you know that he was active in that date.

in your sql now , you cant know that they are active or when they logged in? if you will have this column it will be like that in your sql

 WHERE last_active_date BETWEEN '2012/01' AND '2012/05'

edited Dec 20 '12 at 12:06

answered Dec 20 '12 at 11:27

echo_Me

37,078
5
58
78

The problem is the ???. I want it to represent each year/month where users where active, starting on the first year/month from date_start and ranging to the last year/month from date_stop. – b00st Dec 20 '12 at 11:33
My table already has that, in the form of date_stop. An example row would be : 1, 'John Doe', 1160863200 (15-10-2006), 1333144800 (31-03-2012) – b00st Dec 20 '12 at 13:10
no u dont have , u showed us your table with just , id , name , date_start , date_stop . where is this column of active ? please show us all your table columns then we can help u . – echo_Me Dec 20 '12 at 13:21
A user is active from date_start until date_stop. I don't want a count of how many people were last active in a given period... – b00st Dec 20 '12 at 14:04

score 0 · Answer 3 · answered Dec 21 '12 at 10:01

You may create table and trigger for this. where active_user is added you must increment value in table. on user exit decrement. after this step count of users will be a current value in table.

this way save your time( because "between" is not lite operation for big table(and if you dont have indexes on field date_start)).

Mysql counting rows in a period of time

3 Answers3