Re: avg() for timestamp

On Sat, Mar 06, 2004 at 21:50:52 -0500, Neil Conway <neilc@samurai.com> wrote:
> It seems to me the following should Just Work:
> 
> nconway=# create table t1 (a timestamp);
> CREATE TABLE
> nconway=# insert into t1 values (now());
> INSERT 17164 1
> nconway=# insert into t1 values (now());
> INSERT 17165 1
> nconway=# insert into t1 values (now());
> INSERT 17166 1
> nconway=# insert into t1 values (now());
> INSERT 17167 1
> nconway=# select avg(a) from t1;
> ERROR:  function avg(timestamp without time zone) does not exist
> HINT:  No function matches the given name and argument types. You may 
> need to add explicit type casts.
> 
> It seems we could add the necessary aggregate function to do this. 
> Seems worth doing to me.
> 
> Any comments?

While there is a way to calculate an average timestamp, I don't think
there is an easy way to do this automatically with say a polymorphic
aggregate. You need to know that there is a related type interval that
can be used to keep track of differences in timestamps and that can be
added back to a timestamp at the end.

While this may or may not be suitable for direct use, it will work
for timestamps. A similar thing could be done for timestampz.
I tried the following out with no rows, a single row, several rows,
and some null rows and it seemed to work.

drop aggregate avg(timestamp);
drop function timestamp_sfunc(timestamp_avg, timestamp);
drop function timestamp_ffunc(timestamp_avg);
drop type timestamp_avg;

create type timestamp_avg as (first timestamp, total interval, num float8);

create function timestamp_sfunc(timestamp_avg, timestamp) returns timestamp_avg immutable language 'sql' as '   select
  case when $2 is null then       $1.first     else       case when $1.first is null then         $2       else
$1.first      end     end,     case when $2 is null then       $1.total     else       case when $1.first is null then
      ''0''::interval       else         $1.total + ($2 - $1.first)       end     end,     case when $2 is null then
  $1.num     else       case when $1.first is null then         ''1''::float8       else         $1.num + ''1''::float8
     end     end
 
';

create function timestamp_ffunc(timestamp_avg) returns timestamp immutable language 'sql' as 'select $1.first +
($1.total/ $1.num)'
 
;

create aggregate avg ( basetype = timestamp, sfunc = timestamp_sfunc, stype = timestamp_avg, finalfunc =
timestamp_ffunc
);