At 12:57 PM 28/05/2009, you wrote:

>--- In IBObjects@yahoogroups.com, "Gordon Hamm" <GHamm@...> wrote:
>>
>> What SQL would you use to get secondsbetween for 2 times? Thanks for the aid.
>>
>>
>
>select (EXTRACT( HOUR FROM TIMEFIELD ) * 3600 ) + (EXTRACT( MINUTE FROM TIMEFIELD ) * 60 ) + EXTRACT( SECOND FROM TIMEFIELD )
>from THETABLE

No, the EXTRACT() function doesn't relate to time INTERVALS at all.

The expression you use depends on the sql type of your data.

-- if you subtract a timestamp from a timestamp (recommended!) then the interval (difference) returned is in days, DECIMAL(18,9), i.e. up to 9 places of decimal for parts of day. Your expression will need to multiply the result by (24 * 60 * 60) to arrive at seconds.

-- if you subtract a TIME from a TIME, the result is in seconds, DECIMAL(9,4), i.e., 4 places of decimal for parts of seconds.

The problem with using TIME type fields is that you have no way to know whether they occurred on the same day. If that doesn't matter, e.g., you are keeping an appointment book or similar for just the daylight hours, then TIME fields would be OK.

If you are using Firebird 2.1.x then look at the DATEDIFF() function. You'll be able to simplify your expression considerably with this.

A reminder - this is not a help list for learning SQL. Use the firebird-support list for that; or an Embarcadero forum in case you are using IB.

Helen