Re: Count rows group by time intervals

--- 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.