staggered query?

[Vincent Ladlad]

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 6: Have you searched our list archives?

http://archives.postgresql.org

[Federico Pedemonte]

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;

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

[Federico Pedemonte]

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;

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

[Greg Stark]

Federico Pedemonte <fe****@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
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)

[Greg Stark]

Federico Pedemonte <fe****@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
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)