Skip site navigation (1) Skip section navigation (2)

Re: Count rows group by time intervals

From: Richard Broersma Jr <rabroersma(at)yahoo(dot)com>
To: Loredana Curugiu <loredana(dot)curugiu(at)gmail(dot)com>, pgsql-novice(at)postgresql(dot)org
Subject: Re: Count rows group by time intervals
Date: 2007-05-10 15:33:55
Message-ID: 886677.42322.qm@web31809.mail.mud.yahoo.com (view raw or flat)
Thread:
Lists: pgsql-novice
--- Loredana Curugiu <loredana(dot)curugiu(at)gmail(dot)com> wrote:

> theme |   receiver        |           date
> ---------+----------------------+------------------------
>  LIA   | +40741775622 |  2007-04-27 00:00:00+00
>
> I would like to count rows group by theme, receiver, and time intervals of
> two days. I don't know how to start.

Another way is to use an auxillary table to join on your required date range.

    SELECT A.theme, A.receiver, COUNT(A.date), 
           Time_range.date_start, Time_range.date_end
      FROM Your_table A
INNER JOIN ( SELECT ( SELECT MIN( date ) FROM Your_table ) + x * INTERVAL '1 day' AS date_start,
                    ( SELECT MIN( date ) FROM Your_table ) + 2 + x * INTERVAL '1 day' AS date_end,
               FROM Generate_series( 1, ( SELECT MAX( date ) FROM Your_table ) -
                                        ( SELECT MIN( date ) FROM Your_table ), 2 )
           ) AS Time_range( date_start, date_end )
        ON B.date_start <= A.date AND B.date_end > A.date
  GROUP BY A.theme, A.receiver, Time_range.date_start, Time_range.date_end;

I hope this helps.
Regards,
Richard Broersma Jr.

P.S. the Generate_series() function is just standing in the place of an auxillary table.

In response to

Responses

pgsql-novice by date

Next:From: Chamara GunaratneDate: 2007-05-10 17:54:15
Subject: Re: Disk and shared buffer cache use
Previous:From: Oliveiros CristinaDate: 2007-05-10 14:53:49
Subject: Re: Fw: Count rows group by time intervals

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group