Date: Thu, 22 Apr 2004 22:09:54 +0200
From: Federico Pedemonte <fepede@email.it>
To: pgsql-general@postgresql.org
Subject: Re: staggered query?
Message-ID: <20040422200954.GF1044@noquarter.fepede.org>
References: <002901c4274b$cb7ace30$643b1cac@Titanium>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <002901c4274b$cb7ace30$643b1cac@Titanium>
User-Agent: Mutt/1.5.5.1+cvs20040105i

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;