Re: Converting interval hour to minute to working days

Jonathan Leffler wrote: >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. > > I checked the spent_time column ans it is "spent_time interval hour to minute". Might there be a conversion from interval to datetime when multiplying by an integer? >(...) >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; > >(...) > > It's a beautiful solution and simple enough for me! :-) And since cast from varchar to decimal is automatic, all it took was: ROUND(CAST(SUM(INTERVAL(0) MINUTE(4) TO MINUTE+ wn.spent_time) AS VARCHAR(7))/60/8, 2) Thanks! Fernando -- Fernando Fernandez MoreData - Sistemas de Informa''o, Lda. http://www.moredata.pt sending to informix-list