Assuming 3 things
Table name - test
Column names - start_time, end_time
Added an id column (int) to distinguish each record in the table
You can go with this..... (my apologies for formatting issues)
with
slots as (
select *
from generate_series(0,1439) as s(slot)
),
slots_hours as (
select slot,
slot / 60 as hour
from slots
),
minutes as (
select id,
date_part('hour', start_time) * 60 + date_part('minute', start_time) as start_minute,
date_part('hour', end_time) * 60 + date_part('minute', end_time) as end_minute
from test
),
minute_slots as (
select id,
slot,
hour
from minutes
join slots_hours
on minutes.start_minute <= slots_hours.slot
and minutes.end_minute > slots_hours.slot
)
select hour,
count(*) / 60.0 as sum,
count(distinct id) as count
from minute_slots
group by hour
I'm certain there are more elegant solutions possible - but you can grasp each step this way.
John
On Mon, Mar 16, 2015 at 2:57 PM, Israel Brewster <israel@xxxxxxxxxxxxxx> 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:452015-03-15 09:30 | 2015-03-15 10:152015-03-15 10:30 | 2015-03-15 11:30Then the results should break out something like this:hour | count | sum-----------------------------8 | 1 | 0.759 | 2 | 1.510 | 3 | 1.511 | 1 | 0.5I 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.-----------------------------------------------Israel BrewsterSystems Analyst IIRavn Alaska5245 Airport Industrial RdFairbanks, AK 99709-----------------------------------------------
