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

[shanil]

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

[Fou-Lu]

If you select in a loop, then it will be very slow. Since you say by minute, that would indicate that you have queried 1440 times just to get the counts. That's way too many times, so many so that you may be crippling the SQL configuration for queries per hour if its set.

What do these returning records look like? You should be able to group by a minute() call, but I'm not sure how / if that is doable during a time range. If you use a range like logonDate and logoffDate, then you can use a mix of SQL and PHP easily for this (unless mysql has a specific method of doing this which I'm not sure of). That would be calculating a simple minute(logonDate) + (hour(logonDate) * 60) AS logonMinutes, minute(logoffDate) + (hour(logoffDate) * 60) AS logoffMinutes, and then given a list of all applicable entries between midnight and midnight PHP can loop each minute and check if the minute is between the logonMinutes and logoffMinutes. PHP could also make direct use of the datetime's themselves and use the DateTime and DateInterval methods to determine if it matches the between.

So the only question is what do the records look like?

[shanil]

Hi Fou-Lu,

Thanks for your help and Sorry for the delay..
My array is look like
Array
(
[0] => Array
(
[0] => Array
(
[user] => Array
(
[count] => 0
) [minute] => 0
[1] => Array
(
[user] => Array
(
[count] => 5
[minute] => 1
)

[2] => Array
(
[user] => Array
(
[count] => 3
[minute] => 2

)

)
-----------------------------
---------------------------
etc
))

How can i create the above like array using php and mysql without slowness ?

Thanks in advance..

[guelphdad]

as suggested in another forum you have posted this same question in, create a calendar table with every single minute of every day created and then use a LEFT JOIN to join your other table to the calendar table.