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

Databases
1

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 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

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

2

What you need to do is convert your timestamp values into a string excluding the seconds entry and group by that. For example:


SELECT DATE_FORMAT(TimestampField, '%Y-%m-%d %H:%i'), COUNT(*) as userCount
FROM table
GROUP BY DATE_FORMAT(TimestampField, '%Y-%m-%d %H:%i');
3

Hi rcashell,

Thanks for your quick 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

4

If there are no records with in that period then it will not display 0. However, this is a situation that should be handled by your application.

5

The only way I know to “fill the gaps” as it were is to create a table with all possible dates you will query (possibly generated when needed) and then LEFT JOIN against that. You could create such a table for several weeks/months/years in advance to speed things up if you like.
I’m not sure on the performance impact of such a table / handing it in your code. Handling in the code is more sound, but the table with dates might be faster depending how many dates you put in there etc.

So basically you’d just a create a table like this


date
-----------
2012-09-29 0:00:00
2012-09-29 0:01:00
2012-09-29 0:02:00
2012-09-29 0:03:00
2012-09-29 0:04:00
2012-09-29 0:05:00
2012-09-29 0:06:00
2012-09-29 0:07:00
2012-09-29 0:08:00
2012-09-29 0:09:00
2012-09-29 0:10:00
2012-09-29 0:11:00
2012-09-29 0:12:00
2012-09-29 0:13:00
2012-09-29 0:14:00
2012-09-29 0:15:00
-- etc

No need for a number of visitors column with value 0 by the way; the JOIN and SUM will fix that for you, since the sum of nothing is 0.

6

this is the answer

LEFT OUTER JOIN times that don’t exist in your data, and zero is your friend