Postgres Pro
Downloads
Home   >   mailing lists

Thread: staggered query?

staggered query?

From
"Vincent Ladlad"
Date:
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

Re: staggered query?

From
"Denis P Gohel"
Date:
Hi Try this..
SELECT Col1 , Col2FROM yourtableWHERE 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
>
>

Re: staggered query?

From
"Vincent Ladlad"
Date:
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 , Col2FROM yourtableWHERE 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