Re: Converting interval hour to minute to working days

F. Fernandez wrote: > I have a small problem. We're registering working hours in "interval > hour to minute" and need to sum and convert them to working 8 hour days. > I'd like to use only SQL, and I've been trying something like > > select sum(extend(spent_time*3,day to minute)) from workdone > > but I'm having trouble with error 561 (Sums and averages cannot be > computed on datetime values). It doesn't appear to have worked out that the first argument to EXTEND is a DATETIME, not an INTERVAL, and consequently that would not have worked if it hadn't been for an earlier error. > Surprisingly, if I sum() before extent(), the error is 1260 (It is not > possible to convert between the specified types) > > select extend(sum(wn.spent_time*3),day to minute) from workdone > > It seems that sum() on intervals works, after all... :-( So it should! I think you need to convert all your intervals to the requisite number of minutes, and then sum those. Then you want to divide by a different type of INTERVAL, producing a decimal number - which is the value in days and fractions of a day. And you might want to round that value? And your comment about 'only SQL' presumably precludes a stored procedure? OK - problems include the fact that you cannot divide one interval by another to get a ratio, and therefore you need to convert suitable intervals into decimal numbers, but you have to do that via an intermediate character string representation. Let's see: x = INTERVAL(0) MINUTE(4) TO MINUTE + wn.spent_time -- number of minutes in the interval. -- result type INTERVAL MINUTE(4) TO MINUTE, big enough to -- store any value derived from INTERVAL HOUR TO MINUTE. y = CAST(x AS VARCHAR(5)) -- string z = CAST(y AS DECIMAL(8)) -- number (floating point) a = SUM(z) -- another number (total minutes worked) b = a / (60 * 8) -- total minutes worked divided by (60 minutes per hour -- times 8 hours per working day) = working days c = ROUND(b, 2) -- total days rounded to 2 decimal places. Putting that lot together: SELECT ROUND(SUM(CAST(CAST((INTERVAL(0) MINUTE(4) TO MINUTE + wn.spent_time) AS VARCHAR(5)) AS DECIMAL(8)) / (60 * 8), 2) FROM WorkDone; I think I'd rather write and use a stored procedure - it would be clearer. You could also sum the intervals first, then coerce the result to string and number beforee dividing. However, you are more likely to run into overflow problems that way (if the aggregate number of hours ever exceeded 99h 59m). I've not proved that it can happen; it might not. This way, you control the results before they get summed - it won't fail for plausible data values. > Any thoughts? We're using IDS 9.21.UC4 on Alpha. You should be upgrading very soon. -- Jonathan Leffler #include <disclaimer.h> Email: jleffler@earthlink.net, jleffler@us.ibm.com Guardian of DBD::Informix v2003.04 -- http://dbi.perl.org/