weeks-grouping.sql

WITH weeks AS (
  SELECT distinct date_trunc('week', dates)::date AS start,
  daterange(date_trunc('week', dates)::date, (date_trunc('week', dates) + interval '7 days')::date) week
  FROM generate_series(date '2013-01-01', now(), '1 day')
  dates ORDER BY 1
)
SELECT weeks.start,
    count(whatever.*) as count
  FROM whatever
  JOIN weeks
    ON weeks.week @> whatever.created_at::date
  GROUP BY 1;

Or

SELECT
  week.ts, count(whatever.*)
FROM
  whatever RIGHT OUTER JOIN generate_series(
                              date_trunc('week', timestamptz '2013-01-01'),
                              now(), interval '1 day'
                            ) week(ts) ON date_trunc('week', whatever.timestamp) = week.ts
GROUP BY
  week.ts
ORDER BY
  week.ts