Am 09.06.2011 18:20, schrieb Richard Broersma:
On Thu, Jun 9, 2011 at 6:43 AM, Andreas<maps...@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.
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
