--- In firebird-support@yahoogroups.com, Redlet <remi@...> wrote:

>
> I was not sure of the total_duration field type in :
>
> > select phone_number, sum(duration - cast('00:00:00' as time)) as

total_duration

> > from phone_calls
> > group by phone_number
> > where ...
>
> Finally, if I want to to be sure I get an integer, I can write :
>
> cast(sum(duration - cast('00:00:00' as time)) as int)
>
> Though, I don't know if this is a good practice :-\.
>

Hi Redlet,

To me anyway, your design seems a bit flawed if your table structure
measures duration as a Time data type. Alan said Timestamp when I
think he meant time in his other post.

Basically, in a dialect 3 firebird database, the logically equivalent
delphi data types are:

TimeStamp ~ TDateTime
Date ~ TDate
Time ~ TTime

It does not make any sense to measure a call length in TTime. If I
made a call at 9:00 am and hung up at 9:15 am, my call was not
12:15am in length. My call length was 15 minutes, the only confusion
comes because we also express 15 minutes as 00:15, but we don't mean
12 am by this.

Where your model really falls over is when the amount of time someone
(or a modem etc) spends on the phone exceeds 24 hours. This can not
be expressed as a time. 300 hours and 25 minutes is a valid total
duration (300:25), but hardly a valid 'time'.

Normally for such a table, I would have a structure like this, using
basic data types just for the example, in the real thing you would
use domains with various constraint rules.

create table phone_calls(
id integer,
phone_number varchar(30),
starttime timestamp,
endtime timestamp,
.....
);

Now:

coalesce(endtime, 'now') - starttime will return to you the elapsed
length of time of the call (the duration if you like) in days. You
could create this as a calculated field if you used it heavily, or
even store it in its own indexed column keeping it populated by
triggers on the table.

By using the coalesce function, you can count the duration-to-now of
a call that hasn't yet ended. So your query would become:

select phone_number, sum(Coalesce(EndTime, 'now' - StartTime) as
total_duration
from phone_calls
group by phone_number
where ...

Total_duration is measured in days in this query, multiply by 1440 to
get it in minutes or by 86400 to get it in seconds.

If you do not store both the starttime and endtime of each call, but
rather receive a number from some other system, then you would use a
duration column, but this column would be some numeric or integer
data type.

If you want me to clarify anything, please ask a specific question
about it.

Adam