How can i fetch all the values in a minute from a table ?

[mohan lal]

Hi All,

I have a field createdTime in a table users with current time stamp as data type. I am trying to display the count of users in every minute in each day . By using the php loop and select query its little bit slow. How can i do this using mysql query ? Is there any method to select the values in a minute using a single query ? My aim is to display the details as

Time userCount
2012-09-28 00:00:00 5
2012-09-28 00:01:00 8
2012-09-28 00:02:00 7
2012-09-28 00:03:00 7
------------------------------------
----------------------------------
2012-09-28 00:59:00 10
2012-09-29 00:00:00 3
2012-09-29 00:01:00 10
2012-09-29 00:02:00 8
2012-09-29 00:03:00 20


( Where 5,8,7 etc are the user counts )

If anyone knows the solution,please help me to sort out this...
Thanks in advance....

[Luuk]

select date_format(createdTime,'%Y%m%d%H%i') D, count(*) 
from users 
where createdTime between '2012-09-01' and '2012-10-01' 
group by date_format(createdTime,'%Y%m%d%H%i');

Please check if there is an index on 'createdTime'
if its not fast enough, please give output of the EXPLAIN followed by the query...

[mohan lal]

Hi Luuk,

Thanks for your reply..
I tried your query like

SELECT DATE_FORMAT( time, '%Y-%m-%d %H:%i' ) , COUNT( * ) AS userCount
FROM uesr
WHERE time
BETWEEN '2012-08-22 00:00:00'
AND '2012-08-22 23:59:59'
GROUP BY DATE_FORMAT( time, '%Y-%m-%d %H:%i' )

Its working correctly..
But if a situation like no user at the time 2012-08-22 00:05:00, Can i display this value as 0 using the query ?
Currently the above query does not consider the '2012-08-22 00:05:00' because no entry for this time value..
Actually i am expecting 60*24 rows for every hours ,ie if there is no users for particular minute i would like to display that field value as 0
Is it possible with the mysql query ?

Thanks

[Luuk]

You should create a table which has 60*24, one for every minute.
Then you could select this table, and left join it to the results above.