To: pgsql-general@postgresql.org
Subject: Re: staggered query?
References: <002901c4274b$cb7ace30$643b1cac@Titanium>
	<20040422200954.GF1044@noquarter.fepede.org>
In-Reply-To: <20040422200954.GF1044@noquarter.fepede.org>
From: Greg Stark <gsstark@mit.edu>
Organization: The Emacs Conspiracy; member since 1992
Date: 23 Apr 2004 00:37:59 -0400
Message-ID: <871xmf48d4.fsf@stark.xeocode.com>
Lines: 53
User-Agent: Gnus/5.09 (Gnus v5.9.0) Emacs/21.3
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii

Federico Pedemonte <fepede@email.it> writes:

> On Wed, Apr 21, 2004 at 10:53:16AM +0800, Vincent Ladlad wrote:
> > 
> > the table contains hundreds of thousands of records.
> > i need to get all the entries/records  at every 10 seconds
> > interval.  example, given a table:
> > 
> > hh/mm/ss | data
> > ---------------
> > 00:00:00   1
> > 00:00:01   2
> > 00:00:02   3
> > 00:00:03   4
> > 00:00:04   5
> > 00:00:05   6
> > 00:00:06   7
> > 00:00:07   8
> > ..
> > ..
> > 
> > my query should return:
> > 00:00:10
> > 00:00:20
> > 00:00:30
> > (etc)
> 
> If I understood your problem, the only solution i found was write a
> simple plpgsql function (read at the end of the mail).

There are plenty of solutions for this using standard SQL or non-standard but
still plain SQL queries.

Do you have exactly one sample for every second? And do you want precisely the
first second of the ten second interval? If so then all you really need are
every row where the seconds are divisible by 10.

select * from table where hhmmss::abstime::integer % 10 = 0;

(there are probably more standard ways of testing if the seconds are divisible
by 10, but this is the first way that came to mind)

If you don't always have a sample for every second and just want the first
sample from each ten second interval you could do something like:

select distinct on (hhmmss::abstime::integer / 10) hhmmss order by hhmmss::abstime::integer / 10;

but i expect that would be slower since it would have to do a big sort.


-- 
greg