Are you looking for something like this for the result for the last 45 days or something else?
TIME MON TUE WED ...
09:00 1 3 4
10:00 5 0 8
On Fri, Sep 27, 2013 at 10:22 AM, Larry Rosenman <ler@lerctr.org> wrote:
I tried(!) to write this as a with (CTE), but failed.
Can one of the CTE experts (or better SQL writer) help me here?
-- generate a table of timestamps to match against select generate_series(date_trunc('day',now()-'45 days'::interval),now()+'1 hour'::inte rval,'1 hour') AS thetime into temp table timestamps;
-- get a count of logged in users for a particular time SELECT thetime,case extract(dow from thetime) when 0 then 'Sunday' when 1 then 'Monday' when 2 then 'Tuesday' when 3 then 'Wednesday' when 4 then 'Thursday' when 5 then 'Friday' when 6 then 'Saturday' end AS "Day", count(*) AS "#LoggedIn" FROM timestamps,user_session WHERE thetime BETWEEN login_time AND COALESCE(logout_time, now()) GROUP BY thetime ORDER BY thetime;