Postgres 13

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
drop table if exists observations;
create table observations(
    id serial primary key,
    owner_id int,
    time timestamp);
insert into observations (owner_id, time)
values
    (1, '2018-04-15 00:10:00'),
    (1, '2018-04-15 00:20:00'),
    (1, '2018-04-15 00:22:00'),
    (1, '2018-04-15 00:33:00'),
    (1, '2018-04-15 01:12:00'),
    (1, '2018-04-15 01:20:00'),
    (1, '2018-04-15 01:22:00');

DROP TABLE



CREATE TABLE



INSERT 0 7


select array(
    select generate_series(
        timestamp '2018-04-15 00:00', 
        '2018-04-15 01:00', 
        '30 minutes'))
                


array


{"2018-04-15 00:00:00","2018-04-15 00:30:00","2018-04-15 01:00:00"}



SELECT 1


select
    count(id),
    width_bucket(
        time,
        array(
            select generate_series(
                timestamp '2018-04-15 00:00', 
                '2018-04-15 01:00', 
                '30 minutes'))
    ) as bucket
from observations
where owner_id = 1
group by bucket
order by bucket;


count
bucket


3
1

1
2

3
3



SELECT 3