Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 4 of 4
  1. #1
    New Coder
    Join Date
    Mar 2011
    Posts
    32
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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....
    Last edited by shanil; 09-29-2012 at 09:42 AM.

  • #2
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,979
    Thanks
    4
    Thanked 2,659 Times in 2,628 Posts
    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?

  • #3
    New Coder
    Join Date
    Mar 2011
    Posts
    32
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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..

  • #4
    Super Moderator guelphdad's Avatar
    Join Date
    Mar 2006
    Location
    St. Catharines, Ontario Canada
    Posts
    2,631
    Thanks
    4
    Thanked 147 Times in 138 Posts
    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.


  •  

    Tags for this Thread

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •