Re: Group by range in hour of day

On Mar 16, 2015, at 2: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.

Oh, right. Of course. I'm on 9.4.0

-----------------------------------------------
Israel Brewster
Systems Analyst II
Ravn Alaska
5245 Airport Industrial Rd
Fairbanks, AK 99709
(907) 450-7293
-----------------------------------------------

>
>>
>> -----------------------------------------------
>> Israel Brewster
>> Systems Analyst II
>> Ravn Alaska
>> 5245 Airport Industrial Rd
>> Fairbanks, AK 99709
>> (907) 450-7293
>> -----------------------------------------------
>>
>>
>>
>>
>>
>
>
> --
> Adrian Klaver
> adrian(dot)klaver(at)aklaver(dot)com