| From: | Paul Jungwirth <pj(at)illuminatedcomputing(dot)com> |
|---|---|
| To: | Israel Brewster <israel(at)ravnalaska(dot)net> |
| Cc: | pgsql-general(at)postgresql(dot)org |
| Subject: | Re: Group by range in hour of day |
| Date: | 2015-03-17 18:44:41 |
| Message-ID: | 55087619.7000408@illuminatedcomputing.com |
| Views: | Raw Message | Whole Thread | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-general |
>> 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
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Steve Crawford | 2015-03-17 18:47:11 | libs and upgrades |
| Previous Message | Adrian Klaver | 2015-03-17 18:30:00 | Re: Group by range in hour of day |