Subject Re: [firebird-support] sum and time fields
Author Helen Borrie
At 11:28 PM 28/05/2006, you wrote:
>Hi all,
>I don't know if this is the right place to ask the following question.

Sure is.

>I'd like to add time fields, something like :
>select phone_number, sum(duration)
>from phone_calls
>group by phone_number
>where ...
>Of course, Firebird doesn't like this.

If this is it and duration is some kind of number type then Firebird loves it.

If duration is a TIME type, then no, you can't SUM any date/time
type. Were you under the impression that the TIME type records a
duration ? It doesn't: it stores the time of day (the time on the
24-hour clock).

What you can do is subtract the starting TIME from the ending TIME
and get a DECIMAL(9,4) result that is in seconds and
hundred-thousandths of seconds. But it is much safer to use
TIMESTAMP type for the start and end date and time, since the TIME
type has a scope of only 24 hours. If somebody starts the call at
20:00:00 and finishes at 01:00:00 you have a problem!

> How is it possible to do ?

If your difficulty is what I guessed, then store two TIMESTAMPS for
start time and end time, and calculate the duration:

select phone_number, sum(end_time - start_time) as Total_duration
from phone_calls
group by phone_number
where ...

Otherwise, provide the definition of duration and the error message
you get when your statement fails.