Subject RE: [firebird-support] Time between two times
Author Leyne, Sean

> I'm wondering what would be the best way to pull a record from the
> database based on a StartTime and EndTime (both time fields) and a
> value of Time.
> I know the BETWEEN command, but I'm wondering how it will be
effected by
> the way time is circular.

BETWEEN is not the right answer.

> For instance, if the StartTime is 3:00 PM and the EndTime is 1:00 AM
> the current time is 5:00 PM, I don't think it will return the correct
> values since it'll see the EndTime, 1:00 AM as part of the same day,
> earlier.

The SQL is:

StartTime_Field => :TargetTime
EndTime_Field <= :TargetTime
OR EndTime_Field < StartTime_Field

> I'm assuming the best way to handle will be within the application
> My first impulse is to build a flag into the app that the user can
> that the end time value extends to the following day.

The above SQL eliminates the need for the flag, although I would
recommend storing a true Date/Time values for the Start and End times.

It would make the logic much easier and faster -- since you could build
a compound index on StartTime and EndTime fields.