Getting bounds in result with width_bucket

I’m trying to do a query using width_bucket and having a column in the results with the bounds of my buckets. Is there a simple way to do this?

My query:

SELECT width_bucket(
            (EXTRACT(epoch FROM S.end - S.start) / 60)::integer, 
            array[0, 15, 30, 45, 60, 75, 90, 1000]
       ) AS buckets,
       count(*)
FROM shipments_site S
WHERE S.deleted IS NULL
GROUP BY buckets
ORDER BY buckets

Result:

buckets | count
1       | 20
2       | 6
3       | 22
4       | 25
5       | 10
6       | 11
7       | 6

What I’m trying to get:

buckets | count    | interval
1       | 20       | [0, 15]
2       | 6        | [15, 30]
3       | 22       | [30, 45]
4       | 25       | [45, 60]
5       | 10       | [60, 75]
6       | 11       | [75, 90]
7       | 6        | [90, 1000]

Answer

As you have a fixed sets of buckets, another option is to join against a list of ranges:

with buckets (bucket) as (
  values 
     (int4range(0,15)),
     (int4range(15,30)),
     (int4range(30,45)),
     (int4range(45,60)),
     (int4range(60,75)),
     (int4range(75,90)),
     (int4range(90,null)
  )
) 
SELECT b.bucket, count(*)
FROM shipments_site S
  JOIN buckets b on (extract(epoch from "end" - start) / 60)::int <@ b.bucket
WHERE S.deleted IS NULL
GROUP BY b.bucket
ORDER BY b.bucket;