|Subject||Re: sum and time fields|
Bah, Monday morning
--- In email@example.com, "Adam" <s3057043@...> wrote:
> --- In firstname.lastname@example.org, 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))
> > > 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
> 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
> 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
> comes because we also express 15 minutes as 00:15, but we don't
> 12 am by this.
should have read '12:15 am by this'
> Where your model really falls over is when the amount of time
> (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,
> 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,
> 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
> a call that hasn't yet ended. So your query would become:
sum((cast(Coalesce(EndTime, 'now') as timestamp) - StartTime) as
group by phone_number