How to opimize a query with a field that has been cast

Josef Gschwendtner wrote:

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)?

Helen Borrie answers:

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