Grouping by Partial Date in PostgreSQL

To be able to count by some subset of a timestamp, date_trunc(field, source [, time_zone ]) can be used to modify the date.

With a table users that only has columns id, created_at, the number of users created per month can be queried with

SELECT date_trunc('month', created_at), COUNT(*) as user_count
  FROM users
 GROUP BY date_trunc('month', created_at)

Which produces

     date_trunc      | count
---------------------+-------
 2021-02-01 00:00:00 |    96
 2021-07-01 00:00:00 |    49
 2021-04-01 00:00:00 |    76
 2021-08-01 00:00:00 |    49
 2021-09-01 00:00:00 |    83
 2021-01-01 00:00:00 |    89
 2021-05-01 00:00:00 |    59
 2021-03-01 00:00:00 |    62
 2021-10-01 00:00:00 |    47
 2021-06-01 00:00:00 |    40

The various available time intervals that can be truncated to are

• microseconds
• milliseconds
• second
• minute
• hour
• day
• week
• month
• quarter
• year
• decade
• century
• millennium