PostgreSQL – Get count of items in a table grouped by a datetime column for N intervals

I have a User table, where there are the following fields.

| id | created_at | username | 

I want to filter this table so that I can get the number of users who have been created in a datetime range, separated into N intervals. e.g. for users having created_at in between 2019-01-01T00:00:00 and 2019-01-02T00:00:00 separated into 2 intervals, I will get something like this.

_______________________________
|       dt            | count |
-------------------------------
| 2019-01-01T00:00:00 |   6   |
| 2019-01-01T12:00:00 |   7   |
-------------------------------

Is it possible to do so in one hit? I am currently using my Django ORM to create N date ranges and then making N queries, which isn’t very efficient.

Answer

Generate the times you want and then use left join and aggregation:

select gs.ts, count(u.id)
from generate_series('2019-01-01T00:00:00'::timestamp,
                     '2019-01-01T12:00:00'::timestamp,
                     interval '12 hour'
                    ) gs(ts) left join
     users u
     on u.created_at >= gs.ts and
        u.created_at < gs.ts + interval '12 hour'
group by 1
order by 1;

EDIT:

If you want to specify the number of rows, you can use something similar:

from generate_series(1, 10, 1) as gs(n) cross join lateral
     (values ('2019-01-01T00:00:00'::timestamp + (gs.n - 1) * interval '12 hour')
     ) v(ts) left join
     users u
     on u.created_at >= v.ts and
        u.created_at < v.ts + interval '12 hour'