How to count users group by time interval

user_14117547 0
Viewed 50

I have a table with user id and created_at of type timestamp, I want to count how many users have created their account in 3 hours interval for a given day. so far I have created this query but I'm not able to get the count for each three hours

with time_cte AS (
    SELECT time_sample from
            generate_series('2021-12-01'::date, '2021-12-01'::date + interval '1 day', interval '3 hour')
                as time_sample
) SELECT time_sample, count(u.id) FROM time_cte
    join users u ON u.created_at::date = '2021-12-01'::date
                                       GROUP BY time_sample;

I am able to get series and count but they are total users count for that day

The output I got

time_sample                count
2021-12-01 00:00:00.000000, 4
2021-12-01 03:00:00.000000, 4
2021-12-01 06:00:00.000000, 4
2021-12-01 09:00:00.000000, 4
2021-12-01 12:00:00.000000, 4
2021-12-01 15:00:00.000000, 4
2021-12-01 18:00:00.000000, 4
2021-12-01 21:00:00.000000, 4
2021-12-02 00:00:00.000000, 4

The output I expect is

time_sample                count
2021-12-01 00:00:00.000000, 0
2021-12-01 03:00:00.000000, 0
2021-12-01 06:00:00.000000, 3
2021-12-01 09:00:00.000000, 1
2021-12-01 12:00:00.000000, 0
2021-12-01 15:00:00.000000, 0
2021-12-01 18:00:00.000000, 0
2021-12-01 21:00:00.000000, 0
2021-12-02 00:00:00.000000, 0
postgresql sql
1 Answers

For PostgreSQL 14 you can use the built-in date_bin function.

select 
  date_bin(interval '3 hours', created_at, date_trunc('day', created_at)) as time_slot,
  count(*) as cnt
from users 
group by time_slot
order by time_slot;

For PostgreSQL versions before 14 you may use this implementation of date_bin.

user_23020320
Related