Subject Re: [firebird-support] minutes between
Author Svein Erling Tysvaer
Why cast to TimeStamp when the datatype is Time? Sure, if the day
matters or the fields are used to calculate a duration that may exceed
24 hours, I agree, but if you have two Time fields (and their definition
is no mistake), I think I'd rather do something like

CASE WHEN EndTime >= StartTime THEN (EndTime-StartTime)/60
ELSE (EndTime-StartTime+86600)/60

to cater for the possibility that the EndTime may be after midnight.

Though I do admit that I use TimeStamp far more often than Time.


Adam wrote:
> --- In, Helen Borrie wrote:
>>> d_dude_2003 wrote:
>>>> I have two TIME variables in FireBird stored proc. How do i
>>>> calculate the integer representing the minutes difference between
>>>> them?
>>>> Is it (TIME2 - TIME1)/60?
>> YES
>> At 09:43 PM 5/07/2007, Magnus Titho wrote:
>>> (Time2 - Time1) * 24 * 60
>> Not so for (TIME - TIME): it is a DECIMAL (9,4) in seconds.
> You should also keep in mind that the TIME datatype does not hold the
> date, so to ask for an interval doesn't really make sense. The number
> of seconds returned are based on the assumption that the two times in
> the calculation fall on the same date. If you roll-over midnight, you
> will get a negative number.
> To avoid this confusion, it is probably best to cast as timestamp
> using the appropriate base dates before the subtraction. This will
> return the number of days (eg. 1.25 = 1 day 6 hours).
> Adam