Received: from golem.jpl.nasa.gov (IDENT:postfix@dialin-097.jpl.nasa.gov [128.149.231.97]) by hub.org (8.10.1/8.10.1) with ESMTP id e72FOuG73844 for ; Wed, 2 Aug 2000 11:24:57 -0400 (EDT) Received: from alumni.caltech.edu (localhost.localdomain [127.0.0.1]) by golem.jpl.nasa.gov (Postfix) with ESMTP id F16567F0B; Wed, 2 Aug 2000 15:27:13 +0000 (UTC) Message-ID: <39883DD1.610A0954@alumni.caltech.edu> Date: Wed, 02 Aug 2000 15:27:13 +0000 From: Thomas Lockhart Organization: Yes X-Mailer: Mozilla 4.73 [en] (X11; I; Linux 2.2.15-4mdksmp i686) X-Accept-Language: en MIME-Version: 1.0 To: Itai Zukerman Cc: pgsql-sql@postgresql.org Subject: Re: Time Aggregates References: <87ittju6e7.fsf@matt.w80.math-hat.com> Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit X-Archive-Number: 200008/10 > I'm currently doing this: > SELECT symbol, date_trunc('minute', posted), > min(price), max(price), avg(price) > FROM trade > GROUP BY symbol, date_trunc('minute', posted); > to get a list of minute-averages of trade prices. I get the feeling > that this is bad form, that I should be doing this some other way. Is > that the case? Looks OK to me. If you are doing this *a lot* (i.e. many more queries than inserts), then you might want to set up another column which contains date_trunc('minute',posted) to avoid the calculation. Something like create table trade ( symbol text, posted timestamp, price integer, mpost timestamp ) then define a rule to update mpost when posted gets set (haven't done that part). - Thomas