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

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;