By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,987 Members | 971 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,987 IT Pros & Developers. It's quick & easy.

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

P: 25
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....
Sep 29 '12 #1
Share this Question
Share on Google+
3 Replies


Expert 100+
P: 1,033
Expand|Select|Wrap|Line Numbers
  1. select date_format(createdTime,'%Y%m%d%H%i') D, count(*) 
  2. from users 
  3. where createdTime between '2012-09-01' and '2012-10-01' 
  4. group by date_format(createdTime,'%Y%m%d%H%i');
  5.  
Please check if there is an index on 'createdTime'
if its not fast enough, please give output of the EXPLAIN followed by the query...
Sep 29 '12 #2

P: 25
Hi Luuk,

Thanks for your reply..
I tried your query like
Expand|Select|Wrap|Line Numbers
  1. SELECT DATE_FORMAT( time, '%Y-%m-%d %H:%i' ) , COUNT( * ) AS userCount
  2. FROM uesr
  3. WHERE time
  4. BETWEEN '2012-08-22 00:00:00'
  5. AND '2012-08-22 23:59:59'
  6. 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
Oct 1 '12 #3

Expert 100+
P: 1,033
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.
Oct 2 '12 #4

Post your reply

Sign in to post your reply or Sign up for a free account.