DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma;
	h=mime-version:in-reply-to:references:date:message-id:subject:from:to
	:cc:content-type;
	b=mGx+8uuRiuK3vgU4uC3M0Vg6K20T9l5ZxlR1EPXbvKAGhT26ud/z50fsjKQRAosgqU
	A/+33qCl32sjCU2i1yhfcMtzXvZzXgrgn8efKFyM2YlFdDitO5Ywsun8R256fvLHkkw9
	Xr8nml0km97W2w+v4S1Gjbdc2HnAK6urM+7O8=
MIME-Version: 1.0
In-Reply-To: <4DF0CDFC.4070802@gmx.net>
References: <4DF0CDFC.4070802@gmx.net>
Date: Thu, 9 Jun 2011 09:20:14 -0700
Message-ID: <BANLkTi=M4QNH1QCaoH6k+dCVL3j5wczEnQ@mail.gmail.com>
Subject: Re: need help with some aggregation magic
From: Richard Broersma <richard.broersma@gmail.com>
To: Andreas <maps.on@gmx.net>
Cc: pgsql-sql@postgresql.org
Content-Type: text/plain; charset=ISO-8859-1

On Thu, Jun 9, 2011 at 6:43 AM, Andreas <maps.on@gmx.net> wrote:

> I have a log-table that stores events of users and projects like this
> ( user_id integer, project_id integer, ts timestamp, event_type integer )
>
> I need an aggregated list of worktime per user, per project, per day.
>
> The users can switch projects during the day so I can't work this out with
> min(ts) and max(ts).

SELECT user_id, project_id, date_trunc(  'day', ts ) as event_day,
              MIN( ts ) AS event_start, MAX( ts ) AS event_end,
              MAX( ts ) - MIN( ts ) AS duration
  FROM Loggingtable
GROUP BY user_id, project_id, date_trunc( 'day', ts )
ORDER BY date_trunc( 'day', ts ), user_id, project_id;


-- 
Regards,
Richard Broersma Jr.