avg on time field not working ?

Database
Enthusiast

avg on time field not working ?

Hello,

I've got 3 fields in my table all are in char(5).

'03:02' as deb
'03:06' as fin
'00:04' as duree

I'm trying to calculate an average on the "duree" field but it fails.

I first cast it as a time to calculate an average.
cast(duree||':00' as time)
and then try to get an average

select CHAINE,avg(cast(duree||':00' as time))
from dwhtrf.PO_SUIVI_DICZ
group by CHAINE

=> gives me invalid operation on an ansi datetime or interval ...

How can i do that ?

Thanks.
1 REPLY
Enthusiast

Re: avg on time field not working ?

Try this
select CHAINE, avg (cast(trim(duree)||':00' as interval hour(4) to second (0) ))
from dwhtrf.PO_SUIVI_DICZ
group by CHAINE