rdbms - User Count for Hour -

i need find count of user in different time eg:

table:

    start_time             |  end_time           |   user_id      ----------------------------------------------------------   1)  2014-11-25 01:23:00  | 2014-11-25 06:37:01 |   254   2)  2014-11-25 01:54l33  | 2014-11-25 02:25:31 |   365   3)  2014-11-25 01:55:36  | 2014-11-25 02:26:32 |   547   4)  2014-11-25 05:16:21  |                     |   485   5)  2014-11-25 05:29:03  | 2014-11-25 06:32:46 |   123 

required result:

time  |  count -------------- 1     |  3 5     |  3 

the user signed in particular interval should calculated in count interval hours eg: user 254 logged in @ 1:23 , logged out @ 06:37. count should in 1,2,3,4,5,6 hrs.

reply me if explanation not clear.

thanks in advance

here's query works, might not efficient.

-- count how many of each count quantity appears select qty, count(*) (-- count number of different hours each user   select user_id, count(*) qty -- don't need user_id here, helps debugging     ( -- list every hour+user combination     select distinct hour, user_id         (-- list every hours on period of interest      select '2014-11-24 00:00:00'::timestamp + generate_series(0,48)*interval '1 hour' hour) periods      join flow on (start_time < hour + interval '1 hour'                    , end_time > hour)   ) hours   group hours.user_id ) user_count group qty 

you'll have adjust number of hours (48) cover range of interest.

basically works way:

• create series of hours of interest (assume clock hours rather user hours)
• list 1 line per user-hour combination
• group user, count of number of hours in each user participated
• group hour count, , see how many of each count used