SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    SitePoint Member
    Join Date
    Mar 2011
    Posts
    21
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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 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. #2
    SitePoint Enthusiast
    Join Date
    Jul 2007
    Location
    San Sebastian, Spain
    Posts
    93
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    What you need to do is convert your timestamp values into a string excluding the seconds entry and group by that. For example:

    Code:
    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. #3
    SitePoint Member
    Join Date
    Mar 2011
    Posts
    21
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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. #4
    SitePoint Enthusiast
    Join Date
    Jul 2007
    Location
    San Sebastian, Spain
    Posts
    93
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    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. #5
    Utopia, Inc. silver trophy
    ScallioXTX's Avatar
    Join Date
    Aug 2008
    Location
    The Netherlands
    Posts
    9,067
    Mentioned
    153 Post(s)
    Tagged
    2 Thread(s)
    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

    Code:
    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.
    Rémon - Hosting Advisor

    SitePoint forums will switch to Discourse soon! Make sure you're ready for it!

    Minimal Bookmarks Tree
    My Google Chrome extension: browsing bookmarks made easy

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,248
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by ScallioXTX View Post
    (possibly generated when needed)
    this is the answer

    LEFT OUTER JOIN times that don't exist in your data, and zero is your friend
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Tags for this Thread

Bookmarks

Posting Permissions

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