Message-ID: <4DF0FB0A.2020801@gmx.net>
Date: Thu, 09 Jun 2011 18:55:38 +0200
From: Andreas <maps.on@gmx.net>
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; de;
	rv:1.9.2.17) Gecko/20110414 Thunderbird/3.1.10
MIME-Version: 1.0
To: Richard Broersma <richard.broersma@gmail.com>
CC: pgsql-sql@postgresql.org
Subject: Re: need help with some aggregation magic
References: <4DF0CDFC.4070802@gmx.net>
	<BANLkTi=M4QNH1QCaoH6k+dCVL3j5wczEnQ@mail.gmail.com>
In-Reply-To: <BANLkTi=M4QNH1QCaoH6k+dCVL3j5wczEnQ@mail.gmail.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Am 09.06.2011 18:20, schrieb Richard Broersma:
> 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;
>
As far as I understand you calculate the duration as the difference 
between the first and last event of a project per day.
There is a problem because a user can work from 08.00 to 10.00 on 
project 1 and then from 10.00 to 12.00 on project 2 and then from 12.00 
to 16.00 on project 1 again.
Then I get   project 1   8 hours  plus  project 2   2 hours   though the 
user actually was just 8 hours there.