Re: Group by range in hour of day

On Mon, Mar 16, 2015 at 3:13 PM, Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com>
wrote:

> On 03/16/2015 02:57 PM, Israel Brewster 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", that is the
>> record should be included in the group if the hour of the day for the
>> group falls anywhere in the range [start,end]. Obviously each record may
>> well fall into multiple groups under this scenario.
>>
>> The goal here is to figure out, for each hour of the day, a) what is the
>> total number of "active" records for that hour, and b) what is the total
>> "active" time for those records during the hour, with an ultimate goal
>> of figuring out the average active time per record per hour.
>>
>> So, for simplified example, if the table contained three records:
>>
>> start | end
>> -----------------------------------------------------
>> 2015-03-15 08:15 | 2015-03-15 10:45
>> 2015-03-15 09:30 | 2015-03-15 10:15
>> 2015-03-15 10:30 | 2015-03-15 11:30
>>
>>
>> Then the results should break out something like this:
>>
>> hour | count | sum
>> -----------------------------
>> 8 | 1 | 0.75
>> 9 | 2 | 1.5
>> 10 | 3 | 1.5
>> 11 | 1 | 0.5
>>
>> I can then easily manipulate these values to get my ultimate goal of the
>> average, which would of course always be less than or equal to 1. Is
>> this doable in postgress? Or would it be a better idea to simply pull
>> the raw data and post-process in code? Thanks.
>>
>
> Do not have an answer for you, but a question:
>
> What version of Postgres are you on?
>
> This will help determine what tools are available to work with.

​The following will give you endpoints for your bounds. Version is
important since "range types" could be very useful in this situation - but
you'd still need to generate the bounds info regardless.​

​
SELECT *
FROM
(SELECT * FROM generate_series('2015-03-15'::timestamptz,
'2015-03-16'::timestamptz, '1 hour'::interval) start (start_ts)) AS s
CROSS JOIN
(SELECT end_ts + '1 hour'::interval AS end_ts FROM
generate_series('2015-03-15'::timestamptz, '2015-03-16'::timestamptz, '1
hour'::interval) e (end_ts)) AS e

You would join this using an ON condition with an OR (start BETWEEN [...]
OR end BETWEEN [...]) - range logic will be better and you may want to
adjust the upper bound by negative 1 (nano-second?) to allow for easier
"<=" logic if using BETWEEN.

​David J.​