Re: Group by range in hour of day

On Mar 16, 2015, at 2:16 PM, Paul Jungwirth <pj(at)illuminatedcomputing(dot)com> wrote:
>
>> I have a table with two timestamp columns for the start time and end
>> time of each record (call them start and end).I'm trying to figure out
>> if there is a way to group these records by "hour of day",
>
> I think you can do this by selecting `FROM generate_series(0, 23) s(h)` and then joining to your table based on `h BETWEEN start AND end`.
>
> Whenever I need to write a time-series aggregate query I reach for generate_series. Mostly that's so I have output rows even when COUNT(*) would be 0, but here it also means that a row from your data can feed into multiple output rows.
>
> I could probably write this out in more detail if you like, but that's the short version. :-)

I think I can work with that :-) Hadn't considered doing a join there, so that's a new approach I can investigate. Thanks!
>
> Good luck!
>
> Paul
>
>
>
>
>
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general