pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedstaggered query?
5+ messages / 4 participants
[nested] [flat]
* staggered query?
@ 2004-04-21 02:53 Vincent Ladlad <vincentl@ntsp.nec.co.jp>
2004-04-22 20:09 ` Re: staggered query? Federico Pedemonte <fepede@email.it>
0 siblings, 1 reply; 5+ messages in thread
From: Vincent Ladlad @ 2004-04-21 02:53 UTC (permalink / raw)
To: vincentl@ntsp.nec.co.jp
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: staggered query?
2004-04-21 02:53 staggered query? Vincent Ladlad <vincentl@ntsp.nec.co.jp>
@ 2004-04-22 20:09 ` Federico Pedemonte <fepede@email.it>
2004-04-23 04:37 ` Re: staggered query? Greg Stark <gsstark@mit.edu>
0 siblings, 1 reply; 5+ messages in thread
From: Federico Pedemonte @ 2004-04-22 20:09 UTC (permalink / raw)
To: pgsql-general@postgresql.org
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;
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: staggered query?
2004-04-21 02:53 staggered query? Vincent Ladlad <vincentl@ntsp.nec.co.jp>
2004-04-22 20:09 ` Re: staggered query? Federico Pedemonte <fepede@email.it>
@ 2004-04-23 04:37 ` Greg Stark <gsstark@mit.edu>
0 siblings, 0 replies; 5+ messages in thread
From: Greg Stark @ 2004-04-23 04:37 UTC (permalink / raw)
To: pgsql-general@postgresql.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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: staggered query?
@ 2004-04-21 07:11 Denis P Gohel <sqllist@coralindia.com>
2004-04-22 08:44 ` Re: staggered query? Vincent Ladlad <vincentl@ntsp.nec.co.jp>
0 siblings, 1 reply; 5+ messages in thread
From: Denis P Gohel @ 2004-04-21 07:11 UTC (permalink / raw)
To: pgsql-sql
Hi Try this..
SELECT Col1 , Col2
FROM yourtable
WHERE to_number(to_char(col1, 'SS'),'99') / 10 ) in
(10,20,30,40,50,00);
HTH
Denis
> ----- Original Message -----
> From: Vincent Ladlad <vincentl@ntsp.nec.co.jp>
> To: <vincentl@ntsp.nec.co.jp>
> Sent: Wednesday, April 21, 2004 8:23 AM
> Subject: [SQL] 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
> >
> >
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 4: Don't 'kill -9' the postmaster
>
>
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: staggered query?
2004-04-21 07:11 Re: staggered query? Denis P Gohel <sqllist@coralindia.com>
@ 2004-04-22 08:44 ` Vincent Ladlad <vincentl@ntsp.nec.co.jp>
0 siblings, 0 replies; 5+ messages in thread
From: Vincent Ladlad @ 2004-04-22 08:44 UTC (permalink / raw)
To: 'Denis P Gohel' <sqllist@coralindia.com>; pgsql-sql
hey thanks! it worked:)
here's how we did it:
select sampletime from data where
(extract(seconds from sampletime)::int)::text
in (14, 17, 19);
-----Original Message-----
From: pgsql-sql-owner@postgresql.org
[mailto:pgsql-sql-owner@postgresql.org] On Behalf Of Denis P Gohel
Sent: Wednesday, April 21, 2004 3:12 PM
To: pgsql-sql@postgresql.org
Subject: Re: [SQL] staggered query?
Hi Try this..
SELECT Col1 , Col2
FROM yourtable
WHERE to_number(to_char(col1, 'SS'),'99') / 10 ) in
(10,20,30,40,50,00);
HTH
Denis
> ----- Original Message -----
> From: Vincent Ladlad <vincentl@ntsp.nec.co.jp>
> To: <vincentl@ntsp.nec.co.jp>
> Sent: Wednesday, April 21, 2004 8:23 AM
> Subject: [SQL] 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
> >
> >
> >
> > ---------------------------(end of
> > broadcast)---------------------------
> > TIP 4: Don't 'kill -9' the postmaster
>
>
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if
your
joining column's datatypes do not match
---
Incoming 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
---
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
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2004-04-23 04:37 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-04-21 02:53 staggered query? Vincent Ladlad <vincentl@ntsp.nec.co.jp>
2004-04-22 20:09 ` Federico Pedemonte <fepede@email.it>
2004-04-23 04:37 ` Greg Stark <gsstark@mit.edu>
2004-04-21 07:11 Re: staggered query? Denis P Gohel <sqllist@coralindia.com>
2004-04-22 08:44 ` Vincent Ladlad <vincentl@ntsp.nec.co.jp>
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