From: Greg Stark <gsstark@mit.edu>
To: pgsql-general@postgresql.org
Subject: Re: staggered query?
Date: 23 Apr 2004 00:37:59 -0400
Message-ID: <871xmf48d4.fsf@stark.xeocode.com> (raw)
In-Reply-To: <20040422200954.GF1044@noquarter.fepede.org>
References: <002901c4274b$cb7ace30$643b1cac@Titanium>
<20040422200954.GF1044@noquarter.fepede.org>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: gsstark@mit.edu, pgsql-general@postgresql.org
Subject: Re: staggered query?
In-Reply-To: <871xmf48d4.fsf@stark.xeocode.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox