Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » DBISAM Technical Support » Support Forums » DBISAM General » View Thread |
Messages 1 to 8 of 8 total |
SUM a Time column in SQL? |
Mon, Dec 3 2007 11:55 AM | Permanent Link |
"Bill Root" | I'm using DBISAM 4.25 Build 7.
It appears that SUM does not work on Time columns in SQL. Is that correct? It also appears that CAST does not support casting Time columns to LargeInt or Integer. What's the best way to sum a Time column in SQL? Thanks in advance, Bill |
Mon, Dec 3 2007 7:35 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | Bill,
<< It appears that SUM does not work on Time columns in SQL. Is that correct? >> Yes, you really can't sum time values. What are you trying to do, sum elapsed times or something similar ? If so, then you should store the values as integers or floats instead. -- Tim Young Elevate Software www.elevatesoft.com |
Tue, Dec 4 2007 1:29 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Bill
Why on earth would you want to sum a time column. I can understand adding 3.1 hours and 4.7 hours but 11:47 and 13:21? Roy Lambert |
Tue, Dec 4 2007 8:00 AM | Permanent Link |
"Robert" | "Roy Lambert" <roy.lambert@skynet.co.uk> wrote in message news:13DBEBC5-CEC4-4C93-A66F-EEC4804F2AF0@news.elevatesoft.com... > Bill > > > Why on earth would you want to sum a time column. All kinds of uses. How many man-hours have we spent on project ABC? What's the average time required to complete process XYZ? Etc. If you have the individual times as timestamp format (end time - start time), you can not do that (at least with DBISAM) unless you first convert the times to float. A "datetime to float" SQL function would be nice, so that you can do it on the fly. > I can understand adding 3.1 hours and 4.7 hours but 11:47 and 13:21? It depends on how the unit (in this case time) is represented. Of course as a programmer it is easier for you to deal with .33 of a year than with "one quarter", but that is not the way humans read dates. Same thing with time. It would be nice if it had been set up as decimal right off the bat, when humans invented timekeeping, but it was not. People don't think "0.33333" of a minute, but "20 seconds". Robert > > Roy Lambert > |
Tue, Dec 4 2007 9:04 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Robert
Summing (and grouping) the difference of time columns I can understand, but not time columns themselves, unless someone has stored the time taken in there and is now trying to get back to it in which case casting it as a float MIGHT work but I doubt it. Roy Lambert |
Thu, Dec 6 2007 11:23 AM | Permanent Link |
"Bill Root" | On Mon, 03 Dec 2007 19:35:22 -0500, Tim Young [Elevate Software]
<timyoung@elevatesoft.com> wrote: > > << It appears that SUM does not work on Time columns in SQL. Is that > correct? >> > > Yes, you really can't sum time values. What are you trying to do, sum > elapsed times or something similar ? If so, then you should store the > values as integers or floats instead. For a report, I compute the difference of two time columns (to get the elapsed time) into a new Time column. For time measurements beyond 60 seconds, it's easier to read an elapsed time as hh:mm:ss rather than "xx seconds". That's why the difference is stored as Time. This report was done awhile ago, and changing it would affect other code. For a report summary, people want to see the total elapsed time (which I compute using SUM). My solution was to EXTRACT hours, minutes, and seconds from the Time column, multiply each as needed to convert to seconds, and then SUM that, in the SELECT statement for the report summary. SUMming the Time column, or a CAST of the Time column to Integer, LargeInt, or Float, would have been easier and simpler. In short, I now have a working, albeit inelegant, solution. -Bill |
Thu, Dec 6 2007 11:24 AM | Permanent Link |
"Bill Root" | On Tue, 04 Dec 2007 08:59:38 -0500, Roy Lambert <roy.lambert@skynet.co.uk>
wrote: > Summing (and grouping) the difference of time columns I can understand, > but not time columns themselves, unless someone has stored the time > taken in there and is now trying to get back to it in which case casting > it as a float MIGHT work but I doubt it. Yes, the Time column stores an elapsed time. CASTing to Float did not work. -Bill |
Thu, Dec 6 2007 2:08 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | Bill,
<< For a report, I compute the difference of two time columns (to get the elapsed time) into a new Time column. For time measurements beyond 60 seconds, it's easier to read an elapsed time as hh:mm:ss rather than "xx seconds". That's why the difference is stored as Time. This report was done awhile ago, and changing it would affect other code. >> For future reference, the actual proper implementation of this would be via date/time intervals, which ElevateDB supports natively. For example, subtracting two time values would give you an hour-msec interval by default that can be summed, etc. -- Tim Young Elevate Software www.elevatesoft.com |
This web page was last updated on Thursday, May 23, 2024 at 07:54 PM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |