How to group time intervals by order of magnitude in Postgres?

Question

I have a bunch of time intervals that I would like to analyze. (Thousands of them.) They range from 0 seconds up to several thousand days, but most of them are less than ten minutes.

I would like to group them by roughly how long they are.

I found that I can use the following to group them by the number of minutes (ignoring the seconds):

select count(*), date_trunc('minute', gap) as rounded from mytable
group by rounded order by rounded
;

This groups the majority of the results into the 0-9 minutes groups.

However, there are still hundreds of rows, because all of the odd amounts (e.g. 21 days 13 hours 58 minutes) are still unique when only truncated to the nearest minute.

What I would like would be to group the values more broadly—for instance, 0-10 minutes, then 10-59 minutes, 1-12 hours, 13-47 hours, 2-10 days, etc. The exact groupings aren't particularly important, but the point is I want to round the times variably, something conceptually similar to rounding them all to two "significant digits."

How can I do this in Postgres?

(Note: The query doesn't have to be efficient; nor does it have to be simple, though simplicity is nice. I'm using Postgres 10.)

Answer 1

A CASE expression can help:

SELECT CASE WHEN gap <= INTERVAL '10 minutes'
            THEN '0-10 minutes'
            WHEN gap <= INTERVAL '59 minutes'
            THEN '10-59 minutes'
            ...
            WHEN gap <= INTERVAL '10 days'
            THEN '2-10 days'
            ELSE 'more than 10 days'
       END AS gap_range
...
GROUP BY gap_range;