Archives of the TeradataForum

Message Posted: Fri, 07 Jun 2002 @ 20:38:30 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

Hi,

Given a list of EndTimes with a datatype of TIMESTAMP how would the average EndTime (just the time, not the date) be calculated, i.e., what time, on average, does event x occur?

For example:

Actual times.

EndTime
2002-06-01 00:06:14
2002-06-01 00:16:09
2002-06-02 00:05:40
2002-06-03 00:05:38
2002-06-04 00:06:04
2002-06-05 00:05:34
2002-06-06 00:05:36
2002-06-07 02:58:37

What is the average Endtime of these values? Averaging Time does not seem to be supported without tearing all the pieces apart and putting them back together, and doing this seems only to result in an approximation and not the true average. StartTime is available.

What we have done so far:

sel trim(cast(ave((extract(hour from endtime)*3600)+
        (extract(minute from endtime)*60)+
        (extract(second from endtime))) as INTEGER)/3600)||
     ':'||
    trim(cast(ave((extract(hour from endtime)*3600)+
        (extract(minute from endtime)*60)+
        (extract(second from endtime))) as INTEGER)/60)||
     ':'||
    trim(CAST(((ave((extract(hour from endtime)*3600)+
        (extract(minute from endtime)*60)+
        (extract(second from endtime)))) MOD 60) AS INTEGER))
from database.table where .........;

and:

sel trim(ave(EXTRACT(HOUR from min_etime)))||
    ':'||
    trim(ave(EXTRACT(MINUTE from min_etime)))||
    ':'||
     trim(ave(EXTRACT(SECOND  from min_etime)))
from
(sel endtime(DATE), min(endtime)
  from database.table
  where "this"
    and "that" group by 1)combo(edate,min_etime);

and:

sel avg(cast(substring(cast(endtime as char(23)) from 11) as time))
  from database.table where .........;

Regards,

Mike

	<Prev	Next>		<<First	<Prev	Next>	Last>>