Received: from maia.hub.org (unknown [200.46.204.183]) by mail.postgresql.org (Postfix) with ESMTP id 1E7E16335ED for ; Wed, 14 Apr 2010 13:12:05 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 57064-04 for ; Wed, 14 Apr 2010 16:11:54 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from sargon.lncsa.com (mail.lncsa.com [213.215.28.11]) by mail.postgresql.org (Postfix) with ESMTP id AA43D632EC8 for ; Wed, 14 Apr 2010 13:11:54 -0300 (ADT) Received: from localhost (localhost.localdomain [127.0.0.1]) by sargon.lncsa.com (Postfix) with ESMTP id B83423022A50 for ; Wed, 14 Apr 2010 18:11:53 +0200 (CEST) X-Virus-Scanned: Debian amavisd-new at lncsa.com Received: from sargon.lncsa.com ([127.0.0.1]) by localhost (sargon.lncsa.com [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id kFWBPoBHlMuE for ; Wed, 14 Apr 2010 18:11:53 +0200 (CEST) Received: from zenon.apartia.fr (zenon.apartia.fr [10.0.3.1]) (using TLSv1 with cipher DHE-RSA-AES256-SHA (256/256 bits)) (Client CN "zenon.apartia.fr", Issuer "ca.apartia.fr" (verified OK)) by sargon.lncsa.com (Postfix) with ESMTP id 976193020C70 for ; Wed, 14 Apr 2010 18:11:53 +0200 (CEST) Received: from galba.apartia.fr (galba.apartia.fr [10.0.3.119]) by zenon.apartia.fr (Postfix) with ESMTP id 58B7F5B940324 for ; Wed, 14 Apr 2010 18:11:53 +0200 (CEST) Received: by galba.apartia.fr (Postfix, from userid 1000) id 33C821D723; Wed, 14 Apr 2010 18:11:53 +0200 (CEST) Date: Wed, 14 Apr 2010 18:11:53 +0200 From: Louis-David Mitterrand To: pgsql-sql@postgresql.org Subject: Re: graphing time series data Message-ID: <20100414161152.GA17805@apartia.fr> Mail-Followup-To: pgsql-sql@postgresql.org References: <20100414145434.GA13682@apartia.fr> <20100414154958.GA16847@apartia.fr> MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: User-Agent: Mutt/1.5.20 (2009-06-14) X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.9 tagged_above=-10 required=5 tests=BAYES_00=-1.9 X-Spam-Level: X-Archive-Number: 201004/55 X-Sequence-Number: 33982 On Wed, Apr 14, 2010 at 06:06:59PM +0200, Pavel Stehule wrote: > I don't understand well. Why you don't use a function date_trunc(), > > select date_trunc('week', created), count(*) > from price > group by date_trunc('week', created) Because if a price doesn't change for more than a week, then some weeks will have bad statistical distribution (i.e not including prices which only have their 'modified' updated). So I (think I) need to (1) generate the weeks separately and (2) average prices that are current for each week. But I could be missing something obvious.