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 <l...@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; > > Thanks for any help at all. > > > -- > Larry Rosenman http://www.lerctr.org/~ler > Phone: +1 214-642-9640 (c) E-Mail: l...@lerctr.org > US Mail: 108 Turvey Cove, Hutto, TX 78634-5688 > > > -- > Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/**mailpref/pgsql-sql<http://www.postgresql.org/mailpref/pgsql-sql> >