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

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

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

"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
>

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

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

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

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