From: Federico Pedemonte <fepede@email.it>
To: pgsql-general@postgresql.org
Subject: Re: staggered query?
Date: Thu, 22 Apr 2004 22:09:54 +0200
Message-ID: <20040422200954.GF1044@noquarter.fepede.org> (raw)
In-Reply-To: <002901c4274b$cb7ace30$643b1cac@Titanium>
References: <002901c4274b$cb7ace30$643b1cac@Titanium>
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;
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: fepede@email.it, pgsql-general@postgresql.org
Subject: Re: staggered query?
In-Reply-To: <20040422200954.GF1044@noquarter.fepede.org>
* 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