Group dates by week in Postgres

Timestamps are everywhere in our postgres tables. To group them together you can use date_trunc to normalize the timestamp by minute, hour, day, etc.

You can also normalize the date by week.

> select date_trunc('week', now());
2018-10-08 00:00:00+00

The above example returns the first day of the week for the current moment which is a Monday. Postgres truncates dates to Monday rather than Sunday.

To group dates by week just use the truncated value in both the select clause and the group clause.

select count(*), 
  date_trunc('week', request_time) 
from requests 
group by 
  date_trunc('week', request_time) 
order by date_trunc desc ;