Skip to content

Instantly share code, notes, and snippets.

@hgmnz
Created December 17, 2014 18:41
Show Gist options
  • Save hgmnz/111962fcb1068f8df8d0 to your computer and use it in GitHub Desktop.
Save hgmnz/111962fcb1068f8df8d0 to your computer and use it in GitHub Desktop.
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;
@msakrejda
Copy link

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

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment