Re: staggered query?

Federico Pedemonte <fepede(at)email(dot)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