How to Exclude Current or Partial Weeks in PostgreSQL

Let's say you have a simple query that groups by week and looks back at the last 4 weeks:

select
  date_trunc('week', created_at), -- or hour, day, month, year
  count(1)
from users
where created_at > now() - interval '4 weeks'
group by 1;

If you ran this query midweek, say on a Wednesday, the current week would only return data from Monday through Wednesday (~3 days). Given the incomplete week, the last data point would look artificially low:

To avoid this dip (and the inevitable questions from your manager), use the date_trunc() function in the where clause:

select
  date_trunc('week', created_at),
  count(1)
from users
where date_trunc('week', created_at) != date_trunc('week', now())
and created_at > now() - interval '4 weeks'
group by 1;

You now omit any data from the current incomplete week, there's no more dip:

There's one more problem. If you ran this query mid-week, the starting point of your "look back period" would be in the middle of the week 4 weeks ago. To guard against incomplete weeks in the beginning of your time range, date_trunc() can help again:

select
  date_trunc('week', created_at),
  count(1)
from users
where date_trunc('week', created_at) != date_trunc('week', now())
and created_at > date_trunc('week',now()) - interval '4 weeks'
group by 1;

Instead of looking back 4 weeks from now(), your query look backs 4 weeks from the beginning of current week. See the difference in below:

select now(); -- Result: 2020-02-05 19:38:26.423589+00
select date_trunc('week',now()); -- Result: 2020-02-03 00:00:00+00