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 5 of 5
  1. #1
    Regular Coder
    Join Date
    Jul 2009
    Location
    Chicago, IL
    Posts
    169
    Thanks
    26
    Thanked 3 Times in 3 Posts

    PostgreSQL Group Data by Every 15 Minutes

    I have a PostgreSQL table with multiple columns including "oper_time" of type "timestamp without time zone". I wanted to output the time as well as the count(*) and group the data by hour and by every 15 minutes. In other words, I want to the output to look like:

    hr | mn | thecount
    ---------------------------
    11 | 00 | 38
    11 | 15 | 52
    11 | 30 | 79
    11 | 45 | 51
    12 | 00 | 54
    12 | 15 | 33

    etc....

    Here's what I have so far, but it tells me I need to group by oper_time or use it in an aggregate function. However, I don't want to group by oper_time, and I'm not sure where to go from here.

    Code:
    SELECT extract(hour from oper_time) as hr, extract(minute from oper_time) as mn,count(*) as thecount 
    FROM log WHERE CAST(oper_time As Date) >= '09/23/2010' 
    GROUP BY CAST(oper_time as Date),extract(hour from oper_time),floor(extract(minute from oper_time)/15) 
    ORDER BY CAST(oper_time as Date),extract(hour from oper_time),extract(minute from oper_time)
    Thanks!
    Last edited by wldrumstcs; 09-24-2010 at 08:38 PM.

  • #2
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,550
    Thanks
    78
    Thanked 4,382 Times in 4,347 Posts
    I have never used PostGres, but clearly your answer can't be right.

    Just the first line makes no sense:
    Code:
    SELECT extract(hour from oper_time) as hr, extract(minute from oper_time) as mn,count(*) as thecount
    If you had records such as
    Code:
     
    7:03
    7:05
    7:12
    then that SELECT would *FORCE* you to get each of those times, INDIVIDUALLY.

    I'm 90% certain that you need to use the same FLOOR expression in the SELECT that you are using in the GROUP BY.
    Except that the FLOOR expression is going to give you values of 0,1,2, and 3. Only. So it needs fixing, as well.

    And, too, since you want to both group by and order by the date-alone, that needs to be in the SELECT.

    To wit:
    Code:
     
    SELECT CAST(oper_time as Date) AS dt, 
           extract(hour from oper_time) as hr, 
           floor(extract(minute from oper_time)/15) * 15 AS mn,
           count(*) as thecount 
    FROM log WHERE CAST(oper_time As Date) >= '09/23/2010' 
    GROUP BY CAST(oper_time as Date),
             extract(hour from oper_time),
             floor(extract(minute from oper_time)/15) * 15
    ORDER BY dt, hr, mn
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • Users who have thanked Old Pedant for this post:

    wldrumstcs (09-24-2010)

  • #3
    Regular Coder
    Join Date
    Jul 2009
    Location
    Chicago, IL
    Posts
    169
    Thanks
    26
    Thanked 3 Times in 3 Posts
    You, sir, are my hero. I am not very good with SQL, as you can tell. That did the trick.

    Thanks!

  • #4
    Super Moderator guelphdad's Avatar
    Join Date
    Mar 2006
    Location
    St. Catharines, Ontario Canada
    Posts
    2,634
    Thanks
    4
    Thanked 148 Times in 139 Posts
    Wldrumstcs

    If you are posting in future re Postgres, please indicate that in your thread title and post in the general database forum rather than this forum which is for mysql only. Syntax can be different between databases.

  • #5
    Regular Coder
    Join Date
    Jul 2009
    Location
    Chicago, IL
    Posts
    169
    Thanks
    26
    Thanked 3 Times in 3 Posts
    Whoops, you are right. Sorry about that!


  •  

    Posting Permissions

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