hi,
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 mi
The ts means the time the user started on a project ?
Or the time he finished?
Or can mean both? If so, how do you can tell one from the other? Different
event_type s ?
Is it correct to assume from your words that an user cannot be in more than
one project at the time? If so, can't be overlappin
The log holds events and the ts is just the timestamp when the event
occured.
The events are kind of "opened form xxx with id xxx", "clicked button
xxx", "switched to record xxx", ... They were primarily meant for
helping me to find possible bugs when the user complains that it doesn't
work but
On Thu, Jun 9, 2011 at 6:43 AM, Andreas 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 pro
Am 09.06.2011 18:20, schrieb Richard Broersma:
On Thu, Jun 9, 2011 at 6:43 AM, Andreas 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 pro
Try this:
select user_id, project_id, date_trunc, sum(sum) FROM (select user_id,
project_id, date_trunc('day', ts), SUM(duration) FROM (select user_id,
project_id, a.ts, ((SELECT MIN(b.ts) FROM log b WHERE b.ts>a.ts AND
(date_trunc('day',a.ts)=date_trunc('day',b.ts)))-a.ts) AS duration
from log a
On 2011-06-09, Andreas wrote:
> Am 09.06.2011 18:20, schrieb Richard Broersma:
>> On Thu, Jun 9, 2011 at 6:43 AM, Andreas 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 nee
