SitePoint Sponsor

User Tag List

Results 1 to 9 of 9
  1. #1
    SitePoint Member
    Join Date
    Feb 2010
    Posts
    9
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    (MySQL) Count rows with variable value interval

    Hello,

    This is my first thread on this forum, and I will try and do it well

    I have tried to use google, the mysql forum, this forum, and experimenting, with varying degree of luck. Problem is that I am not very experienced with databases.

    The problem:
    I have a table with unix timestamps. The first three entries are all within the same minute, then there is a gap of two minutes with zero entries, and eventually in the fourth minute there are three more entries:

    Code:
    +---------------+
    | unixTimestamp |
    +---------------+
    |               |
    |   1266416921  |
    |   1266418721  |
    |   1266420471  |
    |   1266428321  |
    |   1266429921  |
    |   1266430821  |
    |               |
    +---------------+
    I would like to ask if somebody could please write me a MySQL query that counts the numbers rows in each minute-period and returns like this. it is important that even those minute-periods that contains zero hits are returned as zero:

    Code:
    +-------+-------+
    |Period | Count |
    +-------+-------+
    |   1   |   3   |
    |   2   |   0   |
    |   3   |   0   |
    |   4   |   3   |
    +-------+-------+
    Thank you for your time

    Kind regards from Norway,
    Marius

  2. #2
    Follow Me On Twitter: @djg gold trophysilver trophybronze trophy Dan Grossman's Avatar
    Join Date
    Aug 2000
    Location
    Philadephia, PA
    Posts
    20,580
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Is it really important, or can you just add those 0's in your program, where display logic belongs?

    If it's really important, you need to create a table with all the period numbers, and join that table.

  3. #3
    SitePoint Member
    Join Date
    Feb 2010
    Posts
    9
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Dan Grossman View Post
    Is it really important, or can you just add those 0's in your program, where display logic belongs?

    If it's really important, you need to create a table with all the period numbers, and join that table.
    Dear Dan Grossman,

    Thank you for you for your reply. Your help is much appreciated! Trust me

    If we skip the zeros, will it be possible for you to write a query that prints the periods with start timestamp of that period (not first stamp in a group of timestamps) instead of period numbers?

    I.e:

    Code:
    +-------------------+-------+
    |Period Start Stamp | Count |
    +-------------------+-------+
    |   1266416921      |   3   |
    |   1266431321      |   3   |
    +-------------------+-------+
    
    // please notice that these periods are exactly 4 mins apart (14400 sec)
    Thank you for your time.

    Kind regards,
    Marius

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,216
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    your test data is inadequate

    perhaps this will get you started in the right direction...
    Code:
    SELECT unixTimestamp
         , FROM_UNIXTIME(unixTimestamp) AS orig_datetime
         , FLOOR(unixTimestamp/60) AS period
         , FROM_UNIXTIME(FLOOR(unixTimestamp/60)*60) AS period_datetime 
      FROM oslo
    ORDER
        BY unixTimestamp
    
    unixTimestamp   orig_datetime       period      period_datetime
    1266416921   2010-02-17 09:28:41   21106948   2010-02-17 09:28:00
    1266418721   2010-02-17 09:58:41   21106978   2010-02-17 09:58:00
    1266420471   2010-02-17 10:27:51   21107007   2010-02-17 10:27:00
    1266428321   2010-02-17 12:38:41   21107138   2010-02-17 12:38:00
    1266429921   2010-02-17 13:05:21   21107165   2010-02-17 13:05:00
    1266430821   2010-02-17 13:20:21   21107180   2010-02-17 13:20:00
    the next step is to use a GROUP BY clause

    want to try it yourself?

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Member
    Join Date
    Feb 2010
    Posts
    9
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    your test data is inadequate

    perhaps this will get you started in the right direction...
    Code:
    SELECT unixTimestamp
         , FROM_UNIXTIME(unixTimestamp) AS orig_datetime
         , FLOOR(unixTimestamp/60) AS period
         , FROM_UNIXTIME(FLOOR(unixTimestamp/60)*60) AS period_datetime 
      FROM oslo
    ORDER
        BY unixTimestamp
    
    unixTimestamp   orig_datetime       period      period_datetime
    1266416921   2010-02-17 09:28:41   21106948   2010-02-17 09:28:00
    1266418721   2010-02-17 09:58:41   21106978   2010-02-17 09:58:00
    1266420471   2010-02-17 10:27:51   21107007   2010-02-17 10:27:00
    1266428321   2010-02-17 12:38:41   21107138   2010-02-17 12:38:00
    1266429921   2010-02-17 13:05:21   21107165   2010-02-17 13:05:00
    1266430821   2010-02-17 13:20:21   21107180   2010-02-17 13:20:00
    the next step is to use a GROUP BY clause

    want to try it yourself?

    Dear R937

    That was very impressive!

    But we have to add a "count" column which tells us how many there are of each period, and I suppose we have to group by each period? I tried doing the above, but it failed, so I must be wrong, hehe:

    Code:
    SELECT unixTimestamp
         , FLOOR(unixTimestamp/60) AS period
         , COUNT(FLOOR(unixTimestamp/60)) as count 
      FROM kingoslo.new_stats 
    GROUP BY period  -- or should this be something else? I think so. Hmmm
    ORDER BY period
    Further help is greatly appreciated!

    Thank you for your time!

    Kind regards,
    Marius

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,216
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    please explain what "it failed" means

    it ran just fine when i tried it

    of course, your test data was inadequate, there was only 1 count for each period, but at least the query did not fail...
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Member
    Join Date
    Feb 2010
    Posts
    9
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    please explain what "it failed" means

    it ran just fine when i tried it

    of course, your test data was inadequate, there was only 1 count for each period, but at least the query did not fail...

    Sure, my terminology is wrong.

    Actually I am wrong all together, it works just fine, the only problem was that I didnt have test data with timestamps from the same minute. When I ran it, I noticed that it only returned count: 1 per period. I automatically thought: "Ah, it didn't work". As SQL Consultant and Database GURU, naturally you are right

    Now to last question: to get rid of the the unix_timestamp column from the result, so that it only displays period and count columns?[/B]

    Your time is greatly appreciated!

    Kind regards,
    Marius Jonsson

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,216
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by king.oslo View Post
    Now to last question I get rid of the the unix_timestamp column from the result, so that it only displays period and count columns?
    have you tried removing it from the SELECT clause of the query?

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  9. #9
    SitePoint Member
    Join Date
    Feb 2010
    Posts
    9
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    WOW! Hehe!

    This solution has taught me a lot of things. I will bookmark it for the future!

    I tried writing on the offical MySQL forum earlier this week, but you were much more helpful than they were.

    Thank you for your time, and best of luck for the future

    Kind regards,
    Marius


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
  •