Re: Group by range in hour of day

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.

>
> -----------------------------------------------
> 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