>> Some weird edge cases to be careful about: activities that cross midnight.
>> Activities that last more than one full day,
>> e.g. start 3/15 and end 3/17.
> Right. And I will run into some of those (at least the crossing midnight),
> so I'll keep an eye out.
If you are running the report on more than one day at a time, I think
David Johnston is right that you want to convert from integers [0, 23]
to timestamps as soon as possible, possibly even just generate a series
of timestamps rather than integers right from the beginning. Also beware
of extract(hour from foo). Probably you want tsrange intersection as
your join condition rather than BETWEEN.
Paul