Received: from maia.hub.org (maia-2.hub.org [200.46.204.251]) by mail.postgresql.org (Postfix) with ESMTP id 7D451B5DC54 for ; Thu, 9 Jun 2011 13:13:49 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.251]) (amavisd-maia, port 10024) with ESMTP id 40133-02-4 for ; Thu, 9 Jun 2011 16:13:42 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from mailout-de.gmx.net (mailout-de.gmx.net [213.165.64.22]) by mail.postgresql.org (Postfix) with SMTP id 178D3B5DC66 for ; Thu, 9 Jun 2011 13:13:37 -0300 (ADT) Received: (qmail invoked by alias); 09 Jun 2011 16:13:34 -0000 Received: from p57BB6483.dip.t-dialin.net (EHLO [192.168.1.113]) [87.187.100.131] by mail.gmx.net (mp070) with SMTP; 09 Jun 2011 18:13:34 +0200 X-Authenticated: #14269776 X-Provags-ID: V01U2FsdGVkX1/ygm8KZXXydhqWJ8aa7F4r+d1yVzxpn1MSfMjNhQ fxDbwHl2btm5d4 Message-ID: <4DF0F130.5070105@gmx.net> Date: Thu, 09 Jun 2011 18:13:36 +0200 From: Andreas 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: pgsql-sql@postgresql.org Subject: Re: need help with some aggregation magic References: <4DF0CDFC.4070802@gmx.net> In-Reply-To: Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 7bit X-Y-GMX-Trusted: 0 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.899 tagged_above=-5 required=5 tests=BAYES_00=-1.9, FREEMAIL_FROM=0.001, RCVD_IN_DNSWL_NONE=-0.0001, T_RP_MATCHES_RCVD=-0.01, T_TO_NO_BRKTS_FREEMAIL=0.01 X-Spam-Level: X-Archive-Number: 201106/25 X-Sequence-Number: 35491 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 can't say what he did or where the error came up. The projects don't overlap per user. So I have time intervals with events for a project and I need to find the first and last event for every interval to add up the time difference and calculate the sum per day. Am 09.06.2011 16:16, schrieb Oliveiros d'Azevedo Cristina: > 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 overlapping, right? > > Best, > Oliveiros > > ----- Original Message ----- From: "Andreas" > To: > Sent: Thursday, June 09, 2011 2:43 PM > Subject: [SQL] need help with some aggregation magic > > >> 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 min(ts) and max(ts). >> >> Is there a clever way to get this with SQL ? >> >> -- >> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) >> To make changes to your subscription: >> http://www.postgresql.org/mailpref/pgsql-sql > >