Forgot telling one thing.
The thousandths values of a second are all unique as long as the second value is the same. For example,

('2011-05-13', '10:11:11.0000', 280.85, 25.0)
('2011-05-13', '10:11:11.0001', 280.85, 25.0)
is ok having the same second value, but having different thousandths values.

('2011-05-13', '10:11:11.0000', 280.85, 25.0)
('2011-05-13', '10:11:11.0000', 280.85, 25.0)
is not allowed because both have the same second values and thousandths values.

However
('2011-05-13', '10:11:11.0000', 280.85, 25.0)
('2011-05-13', '10:11:12.0000', 280.85, 25.0)
is ok, because the second values are different even though both having the same thousandths values.


Therefore, the table T_10199000 will never have the same thousandths values within a second.
This is guaranteed.

HR

--- In firebird-support@yahoogroups.com, "ehaerim" <ehaerim@...> wrote:
>
>
> 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]
>