--- In firebird-support@yahoogroups.com, Mitch Peek <mpeek@...> wrote:

>
> Helen Borrie wrote:
> > 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
> >
> >
> Helen,
> Would this approach be better then an index on the timestamp and

just

> querying between Adate and Adate+1? if so, why?
> The only problem I see might be the inclusion of the record which could
> occur if an entry was exactly at midnight (00:00:00.0000)

For the exact reason you point out (confusion with the 'between'
operator being inclusive).

You could certainly do

where DyStart >= :StartDate and DyStart < (:StartDate + 1)

But

where DyStart between :StartDate and (:StartDate + 1)

will give the wrong result because it includes midnight the morning after.

Helen's first response is the correct way to do it, but it only works
in Firebird 2 or higher (new feature). The other suggestion is a
'hack' to achieve roughly the same thing in Firebird 1.5 or earlier
which don't have a clue about expression indices.

Adam