roar4711 wrote:

> Hi,
>
> Im facing a problem where I need to query time series information from
> a table stored in a FB 2.1 datbase.
>
> The problem is that I need to query one record before and after the
> actual period specified in the select statement.
>
> Example table:
>
> Time Data
>
> 1 X
> 2 X
> 3 X
> 4 X
> 5 X
>
>
> select Data from XY where time>= 2 and time<=4
>
> Here I need record with time 1 and 5 also.
>
> Easy you might think, but the problem is that the timeserie is not
> uniform, the table might look like this:
>
>
> Time Data
>
> 1 X
> 5000 X
> 5100 X
> 5200 X
> 5400 X
> 5700 X
> 9000 X
>
> select Data from XY where time>= 5000 and time<=5700
>
> Here I also need record with timestamp 1 and 9000.
>
> Any suggestions how to solve the problem is very welcome.
>

A quick sugestion.. don't know how it will perform.

select
Data
from
XY
where
time >= (select max(time) from XY where Time < 5000) and
time <= (select min(time) from XY where Time > 5700)

You will need both ASCENDING and DESCENDING indices on XY.TIME.

You will get more than one record on each side if Time could have
repeated values.

see you !


--
Alexandre Benson Smith
Development
THOR Software e Comercial Ltda
Santo Andre - Sao Paulo - Brazil
www.thorsoftware.com.br