I can't figure out how to subtract two timestamp fields to get the
difference in hours. In:

SELECT StartTime,EndTime,
((EndTime - StartTime) Hour) As ElapsedTime
FROM  MyTable

I understand ElapsedTime is an Inverval value but how do I convert
that to actual hours? Dividing by 3600000 as I would in DBISAM gives
an error.

The Interval section seems to be missing in the help file and the web
help does not seem to explain this very well.

Michael,

<< I understand ElapsedTime is an Inverval value but how do I convert that
to actual hours? Dividing by 3600000 as I would in DBISAM gives an error. >>

Since the VCL doesn't have an interval field type, EDB returns intervals in
the raw/native format. You need to cast the result of the interval
expression to an integer or a character string, e.g.:

select
 EndTime,
 StartTime,
 cast((EndTime-StartTime) hour as integer) ElapsedTime
from MyTable

Ole Willy Tuv

On Tue, 29 May 2007 20:00:40 +0200, "Ole Willy Tuv" <owtuv@online.no>
wrote:

>Michael,
>
><< I understand ElapsedTime is an Inverval value but how do I convert that
>to actual hours? Dividing by 3600000 as I would in DBISAM gives an error. >>
>
>Since the VCL doesn't have an interval field type, EDB returns intervals in
>the raw/native format. You need to cast the result of the interval
>expression to an integer or a character string, e.g.:
>
>select
>  EndTime,
>  StartTime,
>  cast((EndTime-StartTime) hour as integer) ElapsedTime
>from MyTable

Thanks Ole. But according to the docs you should be able to use float
too. For me it gives an error. What I need is the exact number of
hours not a rounded integer.

Michael,

<< What I need is the exact number of hours not a rounded integer. >>

cast((EndTime-StartTime) minute as integer)/60
cast((EndTime-StartTime) second as integer)/3600
cast((EndTime-StartTime) hour to minute as varchar)
cast((EndTime-StartTime) hour to second as varchar)

Ole Willy Tuv

I forgot the millisecond precision:

cast((EndTime-StartTime) msecond as bigint)/3600000
cast((EndTime-StartTime) hour to msecond as varchar)

Ole Willy Tuv

Michael,

<< Thanks Ole. But according to the docs you should be able to use float
too. For me it gives an error. What I need is the exact number of hours not
a rounded integer. >>

I think you're getting the Object Pascal TDateTime type mixed up with the
ElevateDB SQL types.  The two are not the same thing.  What Ole responded
with was correct - if you want the exact value down to the milliseconds,
then you could use division to get a float value that represents fractions
of an hour or use an "hour to msecond" specifier after the subtraction.

--
Tim Young
Elevate Software
www.elevatesoft.com