Re: Count rows group by time intervals - Mailing list pgsql-novice

--- Loredana Curugiu <loredana.curugiu@gmail.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.