On Mar 16, 2015, at 2:16 PM, Paul Jungwirth <p...@illuminatedcomputing.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@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
