Thanks! I did not know such a function exists.
From: Nicklas Avén [mailto:nicklas.a...@jordogskog.no] Sent: Thursday, December 23, 2010 3:31 PM To: Satish Burnwal (sburnwal) Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] Need Help in query Hallo This I think should work. To get the usernames by hour as you describe: SELECT h.hour, usrlog.userid (select generate_series(1,24) as hour) as h inner join usrlog on h.hour >= usrlog.loginhr and h.hour <= usrlog.logouthr order by h.hour, usrlog.userid; To get the number of users per hour : Select h.hour, count(*) asNumberOfUsers (select generate_series(1,24) as hour) h inner join usrlog on h.hour >= usrlog.loginhr and h.hour <= usrlog.logouthr group by h.hour; HTH Nicklas 2010-12-23 skrev Satish Burnwal (sburnwal) : I need a help in writing a query. I have data as follows: > >mydb=# select * from usrlog ; > logid | userid | loginhr | logouthr >-------+----------+---------+---------- > 0 | sburnwal | 0 | 1 > 1 | rickyrs | 1 | 5 > 2 | satishbn | 1 | 6 > 3 | taohujin | 2 | 4 > 4 | jospehm | 4 | 5 > > >Table captures the login and logout time (taking hour here to simplify) >of users and my aim to find the number of logged-in users (online users) >at every hr (1st hr i.e. 0-1, 2nd hrs i.e. 1-2, 3rd hr i.e. 2-3 and ... >so on). As the data indicates, use is not logging out in same hr as hr >of login. A user can be logged-in for more than one hr. For example, >here user rickyrs is logged-in for 1st, 2nd, 3rd, 4th and 5th hr. My >query needs to find out in the last 24 hrs, how many users were >logged-in at each hr. I want the result this way: > >Nth-hr user >--------------------------- >1 sburnwal >2 rickyrs >2 satishbn >3 rickyrs >3 satishbn >3 taohujin >4 rickyrs >4 satishbn >4 taohujin >4 josephm > >Appreciate your response in advance. For me, even the count of users on >hourly basis is fine. > >Thanks >-Satish > >-- >Sent via pgsql-general mailing list (pgsql-general@postgresql.org) >To make changes to your subscription: >http://www.postgresql.org/mailpref/pgsql-general > >
