Today I Learned

A Hashrocket project

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 ;
Looking for help? Hashrocket developers believe that data quality is as important as code quality. We enjoy all the challenges of relational databases, from finding the fastest index, to structuring data to fit the needs of an application. We're eager to share our experiences; check out PG Casts, a series of free weekly PostgreSQL screencasts.