In the link below, I already asked a more general question and got a
thankful reply from HTH.

http://tech.groups.yahoo.com/group/firebird-support/message/113514
<http://tech.groups.yahoo.com/group/firebird-support/message/113514>

Owing to my lack of sql knowledge, I am not fully understanding it yet.

So, I will change the original question to limit with the same date
only. That is, the general search range in the previous post was
[(FromDate, FromTime), (ToDate, ToTime)]. Now, I will change it within
the same date and therefore the simplier search range is [FromTime,
ToTime]. Here "[" and "]" means inclusive search.

Here it goes the question again with search range within the same date:

A table is created using

CREATE TABLE T_10199000(D DATE NOT NULL, T TIME NOT NULL, P DOUBLE
PRECISION NOT
NULL, V FLOAT NOT NULL, PRIMARY KEY (D, T), CHECK(V >= 0));

Given a search range, [FromTime, ToTime], where both ends are inclusive.

[1] I would like to find out all the ranges each of which is either
contiguous
or empty. Here "contiguous" means the seconds values are in a row in
ascending
order without any gap. "Empty" means literally a vacant range without
any data.

(ex)
Following 7 records were inserted into the table.
INSERT INTO T_10199000(D, T, P, V) VALUES('2011-05-13', '10:11:11.0000',
280.85, 25.0);
INSERT INTO T_10199000(D, T, P, V) VALUES('2011-05-13', '10:11:12.0000',
280.85, 25.0);
INSERT INTO T_10199000(D, T, P, V) VALUES('2011-05-13', '10:11:12.0001',
280.85, 25.0);
INSERT INTO T_10199000(D, T, P, V) VALUES('2011-05-13', '10:11:15.0000',
280.85, 25.0);
INSERT INTO T_10199000(D, T, P, V) VALUES('2011-05-13', '10:11:15.0001',
280.85, 25.0);
INSERT INTO T_10199000(D, T, P, V) VALUES('2011-05-13', '10:11:18.0000',
280.85, 25.0);
INSERT INTO T_10199000(D, T, P, V) VALUES('2011-05-13', '10:11:18.0001',
280.85, 25.0);

When this table is searched for [10:11:11, 10:11:18], it should return 3
contiguous ranges and 2 empty ranges.
- 3 contiguous ranges :
-- [10:11:11, 10:11:12]
-- [10:11:15, 10:11:15]
-- [10:11:18, 10:11:18]
and
- 2 empty ranges:
-- [10:11:13, 10:11:14]
-- [10:11:16, 10:11:17]

Maybe getting either contiguous or empty ranges would suffice because
then I would be able to get the other ranges by calculation. That is,
if 2 emtpy ranges are returned from the query, 3 contiguous ranges can
be calculated easily, or vice versa.


[2] Next, for each range,
if it is a contiguous range, I would retrieve all the records belonging
to that range.
if it is an empty range, I would do request data for that emtpy range
from external data source and insert them into that missing range.

[3] Finally, after iterating all the ranges, the table T_10199000 will
have no empty range left.



Please modify the answer from the previous answer or write a more
appropriate answer for this limited/simplified search range.

I wish I can someday write a code to help others... :-)

HR





[Non-text portions of this message have been removed]