Subject Re: [firebird-support] sum and time fields
Author Helen Borrie
At 02:05 AM 29/05/2006, you wrote:
>Thanks Helen and thanks Alan for your answers,
>Helen suggested :
> > select phone_number, sum(end_time - start_time) as Total_duration
> > from phone_calls
> > group by phone_number
> > where ...
>Perhaps something like :
>select phone_number, sum(duration - cast('00:00:00' as time)) as
>from phone_calls
>group by phone_number
>where ...
>What do you think of this ?

It's wrong.

>It seems it is correct for Firebird 1.5 (I mean there is no error
>message and the result is what I expect) but I'm not sure what the type
>of the result is.

Assuming 'duration' is a TIME type, it will be a DECIMAL(9,4) value
which won't be anything useful as data.

>When I execute the SQL script with IBConsole which
>comes with Delphi, it works fine, but when I try to get the field
>total_duration value in a FreePascal Integer variable, FreePascal talks
>about a conversion error.

You still haven't got the point.

A TIME type is not a duration or 'interval of time'.

It is a date/time type, representing the time of day. For example,
if you see the value '20:25:15.0000', it is storing the time '8:25:15
PM'. In the database the same value is a DECIMAL(9,4) representing
'number of seconds since midnight on the day that the value was
stored', i.e. 73515.0000 (actually, 735150000 with a scale of
4). You cannot access the value in this form.

>Thanks again for your precious help.

It our help is so precious, why do you continue to refuse to believe
what we are telling you....