Subject Re: R: [ib-support] SUM( TIME FIELD )
Author Ann Harrison
At 06:53 PM 2/10/2001 +0100, Arnaldo wrote:

> > >I do:
> > >Select SUM( TIMEFIELD ), but cannot find a result.

The error was :
Statement failed, SQLCODE = -901
data type not supported for arithmetic

>I have a field in which i store a difference from two fields.

The difference between two times is not a time, it's a duration.
The time datatype represents an instant in time, not a length of
time. You wouldn't expect to sum two dates, right? This is the
same thing. What do you get if you add eight o'clock in the
morning to five o'clock in the afternoon? Cognitive dissonance.

The time datatype is not closed over addition and
subtraction. Multiplication and division are not
defined for time. Duration is different. If you
add one duration to another you get another duration.

5 hours + 6 hours = 11 hours
6 hours - 5 hours = 1 hour
4 hours * 4 = 16 hours
4 hours / 12 = .333 hours

Duration behaves like other numeric types.

Time is different.

07:30 + 3 = 10:00

A time plus a number produces a time. The "3" is
a number representing a duration of three hours.

12:00 - 8:00 = 4

A time subtracted from a time produces a number -
that's not four o'clock in the morning, but a
duration of 4 hours.

>So in this field I have: 7.30. in the second field : 8.30
>These are the hours they have worked.
>Now I MUST SUM all the Working Hours of this field.
>so this two values must give 16.00 working hours.

Use a numeric datatype to hold duration - hours worked,
that sort of thing.


We have answers.