https:

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

 < Last>>

 Subj: Average Point in Time? From: Mike Vanole

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

 < Last>>

Attachments

Library

Quick Reference

Archives

Sample Index

 2016 2007 2015 2006 2014 2005 2013 2004 2012 2003 2011 2002 2010 2001 2009 2000 2008 1999

2002 Indexes

 Jan Jul Feb Aug Mar Sep Apr Oct May Nov Jun Dec

 Top Home Privacy Feedback