Subject Re: [firebird-support] Date Arithmatic
Author Helen Borrie
At 02:35 PM 18/11/2004 +0200, you wrote:

>What does ( Date 1 - Date 2 ) produce, I see a result as such
>0.590934534 etc....
>Could someone please explain?

The result is in days.

>What I am trying to achieve is to get the hours and minutes between two
>date entries.
>Both date entries are in the format where date and time is recorded.

Timestamp, yes?

>The calculation should take into account the date part as well meaning
>that the hours calculated can exceed 24.
>The best solution would be if the Calculation would be done in a
>computed field.

The result of subtracting or adding date or time types is an interval, in
days. To store it like you want, you would first have to calculate that
number into hours and minutes, discarding the rest; do some parsing to
convert these individual parts to number literals and then concatenate
these literals with the appropriate separator characters. (Unless you have
the two timestamps constrained not to be more than 23 hours and 59 minutes
apart, you'd also need to cater for days and maybe months and years as well.

There might be a UDF around that could do it; or you could write a stored
procedure that gets called by a Before Update trigger when the second date
is written, calculates the dd:hh:mm string you want and passes it back for
storage in a character field, e.g. time_interval char(8).