Re: need help with some aggregation magic

On Thu, Jun 9, 2011 at 6:43 AM, Andreas <maps(dot)on(at)gmx(dot)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.