Received: from zukerman-1.dsl.speakeasy.net (zukerman-1.dsl.speakeasy.net [216.254.75.142]) by hub.org (8.10.1/8.10.1) with ESMTP id e72Fq4G81128 for ; Wed, 2 Aug 2000 11:52:04 -0400 (EDT) Received: from (matt.w80.math-hat.com) [216.254.75.141] by zukerman-1.dsl.speakeasy.net with esmtp (Exim 3.12 #1 (Debian)) id 13K0nS-0004UI-00; Wed, 02 Aug 2000 11:51:06 -0400 Received: from zukerman by matt.w80.math-hat.com with local (Exim 3.12 #1 (Debian)) id 13K0oL-0003Eb-00 for ; Wed, 02 Aug 2000 11:52:01 -0400 To: pgsql-sql@postgresql.org Subject: Re: Time Aggregates References: <87ittju6e7.fsf@matt.w80.math-hat.com> <39883DD1.610A0954@alumni.caltech.edu> From: Itai Zukerman Date: 02 Aug 2000 11:52:01 -0400 In-Reply-To: Thomas Lockhart's message of "Wed, 02 Aug 2000 15:27:13 +0000" Message-ID: <87em47u0da.fsf@matt.w80.math-hat.com> Lines: 31 User-Agent: Gnus/5.0807 (Gnus v5.8.7) Emacs/20.7 MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii X-Archive-Number: 200008/11 > > SELECT symbol, date_trunc('minute', posted), > > min(price), max(price), avg(price) > > FROM trade > > GROUP BY symbol, date_trunc('minute', posted); Hmmm... I'm not sure how to go about doing this for, say, 5 minute intervals. Basically, I want a function: date_round( timestamp, timespan ) --> timestamp that will round the timestamp to the "nearest" timespan interval, counting back from, say, the current time. date_round( '1/1/99 15:21', '5 minutes' ) --> '1/1/99 15:20' if the current time is, say, 12:00. As I see it, this involves 1. calculating the interval between now and the timestamp; 2. rounding that interval to the nearest multiple of the supplied interval; 3. adding the rounded interval to the current time. It's step 2 that I can't quite figure out. I'm thinking about using date_part( 'epoch', ... ) and some arithmetic. Will that be OK? Has anyone done this before? -itai PS. I'll also be happy if the function is called 'date_trunc' :) PPS. I expect this query to be called much less often than inserts to the table.