Re: Group by range in hour of day - Mailing list pgsql-general

> On Mar 17, 2015, at 8:09 AM, Paul Jungwirth <pj@illuminatedcomputing.com> wrote:
>
>>> test=> select h, count(*) from start_end, generate_series(0, 23) as s(h) where h between extract(hour from
start_time)and extract(hour from end_time) group by h order by h; 
>>>
>>> h  | count
>>> ----+-------
>>>  8 |     2
>>>  9 |     3
>>> 10 |     2
>>> 11 |     2
>
> Note if you always want all 24 rows with a count of 0 when appropriate (which seems common in reports with tables or
plots),you can just tweak the above query to use a left join: FROM generate_series(0, 23) AS s(h) LEFT OUTER JOIN
start_endON h BETWEEN ... 
>
> Paul

Right, thanks. That makes sense. So next question: how do I get the "active" time per hour from this? To use the same
examplethat came up with this result set: 

Given this:

test=> select * from start_end ;
id |       start_time       |        end_time
----+------------------------+------------------------
 1 | 2015-03-16 08:15:00-07 | 2015-03-16 09:35:00-07
 2 | 2015-03-16 09:15:00-07 | 2015-03-16 11:05:00-07
 3 | 2015-03-16 08:00:00-07 | 2015-03-16 11:45:00-07

The specified query returns this:

h  | count
----+-------
 8 |     2
 9 |     3
10 |     2
11 |     2

Which is an excellent start, but I also need one more column, which is the total "active" time per hour. So given the
intermediateresult of this: 

id |       start_time       |        end_time        | h
----+------------------------+------------------------+----
 1 | 2015-03-16 08:15:00-07 | 2015-03-16 09:35:00-07 |  8
 3 | 2015-03-16 08:00:00-07 | 2015-03-16 11:45:00-07 |  8
 1 | 2015-03-16 08:15:00-07 | 2015-03-16 09:35:00-07 |  9
 2 | 2015-03-16 09:15:00-07 | 2015-03-16 11:05:00-07 |  9
 3 | 2015-03-16 08:00:00-07 | 2015-03-16 11:45:00-07 |  9
 2 | 2015-03-16 09:15:00-07 | 2015-03-16 11:05:00-07 | 10
 3 | 2015-03-16 08:00:00-07 | 2015-03-16 11:45:00-07 | 10
 2 | 2015-03-16 09:15:00-07 | 2015-03-16 11:05:00-07 | 11
 3 | 2015-03-16 08:00:00-07 | 2015-03-16 11:45:00-07 | 11

I'd want a final result of this:

h  | count  |  sum
----+---------------
 8 |     2    | 1.75 (or 1:45:00 or whatever)
 9 |     3    | 2.33 (2:20:00)
10 |     2   | 2.00 (2:00:00)
11 |     2   | 0.83 (0:50:00)


Where the 1:45 in the 8 hour is based on 45 minutes from row id 1 [8:15-9:00) plus the full hour [08:00-9:00) from row
id3, the hour 9 value is based on the amount of rows 1,2 and 3 that fall within the 9 hour, etc. 
-----------------------------------------------
Israel Brewster
Systems Analyst II
Ravn Alaska
5245 Airport Industrial Rd
Fairbanks, AK 99709
(907) 450-7293
-----------------------------------------------

>
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general