At 12:55 AM 19/06/2007, you wrote:

>Hi,
>
>we have a big table (several million records) and like to select the
>records regarding one day:
>
>Within the table there is a field (DyStart) which is a Timestamp.
>A convienient way to select would be:
>select * from DY where cast(DyStart as Date) = :DayOfInterest (e.g.
>'31.6.2007')
>
>Sadly this select cann't use the index on field DyStart.
>
>Therefore the select has to be something like:
> ... where DyStart >= :DayOfInterest and DyStart < :DayOfInterest + 1
>
>Can you think about something more convenient?
>Is there a way to optimize the first select (with cast)?

In Firebird 2.0.x you can have an expression index - see release notes.

In Fb 1.5.x and below you'd need to add a trigger-populated proxy
column for DyStart and index that, e.g.

Alter DY add DyStartdate DATE;

create index ascDyStartdate on DY (DyStartdate);

create trigger biu_DY for DY
active before insert or update
as
declare vtimestamp timestamp;
begin
if (new.DyStart is not null) then
new.DyStartdate = cast (new.DyStart as Date);
end
else
if (updating and old.DyStart is not null) then
new.DyStartdate = null;
end

Then
select * from DY where DyStartdate = :DayOfInterest

./heLen