--- In firebird-support@yahoogroups.com, "Adam" <s3057043@...> wrote:
>
> --- 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.

should have read '12:15 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:
>

Should be:

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