Thread: staggered query?

hi! im new to SQL, and i need to find a solution
to this problem:

i have a table with two columns, the first column
is of type timestamp.

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)

is this possible? if yes, how do i do it?

thanks!

---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.484 / Virus Database: 282 - Release Date: 5/27/2003

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).

Don't know if it is the best solution but it works !

Ciao,
    Federico.

------------------------------------------------------------------------

Usage :

 select * from timetable ('23/06/1974 18:15', '23/06/1974 20:30', '00:10');
      timetable
---------------------
 1974-06-23 18:15:00
 1974-06-23 18:25:00
 1974-06-23 18:35:00
 1974-06-23 18:45:00
 1974-06-23 18:55:00
...


CREATE OR REPLACE FUNCTION timetable(timestamp, timestamp, interval) RETURNS SETOF timestamp
    AS '
DECLARE
        inizio        alias for $1;
          fine          alias for $2 ;
    inter         alias for $3;

    tt timestamp;
BEGIN
    tt := inizio;

    while tt <= fine loop
            return next tt;
        tt:=tt+inter;
    end loop;
    RETURN ;
END;
   '
LANGUAGE plpgsql IMMUTABLE;

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