Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB General » View Thread |
Messages 1 to 6 of 6 total |
Subtracting timestamp fields |
Tue, May 29 2007 1:13 PM | Permanent Link |
Michael Fullerton | 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. |
Tue, May 29 2007 2:03 PM | Permanent Link |
"Ole Willy Tuv" | 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 |
Tue, May 29 2007 6:29 PM | Permanent Link |
Michael Fullerton | 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. |
Tue, May 29 2007 7:04 PM | Permanent Link |
"Ole Willy Tuv" | 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 |
Tue, May 29 2007 7:15 PM | Permanent Link |
"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 |
Tue, May 29 2007 8:23 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | 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 |
This web page was last updated on Tuesday, April 30, 2024 at 03:55 PM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |