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?

-Neil

Sat, 06 Mar 2004 21:50:52 -0500
Neil Conway <neilc@samurai.com>
avg() for timestamp
Neil Conway <neilc@samurai.com> writes:
> It seems we could add the necessary aggregate function to do this. 
> Seems worth doing to me.

Please add it for both timestamp with/without time zone.  AFAICS the
same underlying code will serve for both, but you'll need separate
entries in the system catalogs.

			regards, tom lane

Sat, 06 Mar 2004 22:49:32 -0500
Tom Lane <tgl@sss.pgh.pa.us>
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
);

Mon, 8 Mar 2004 01:44:00 -0600
Bruno Wolff III <bruno@wolff.to>
Re: avg() for timestamp
Bruno Wolff III <bruno@wolff.to> writes:
>   Neil Conway <neilc@samurai.com> wrote:
>> It seems to me the following should Just Work:
>> nconway=# select avg(a) from t1;
>> ERROR:  function avg(timestamp without time zone) does not exist

> 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.

Given that this would be done with C code, I doubt we'd go to the
trouble of implementing it that way.  We'd just cheat: add up the 
numeric values of the timestamps and divide at the end.  float8
makes a perfectly fine accumulator ;-)

			regards, tom lane

Mon, 08 Mar 2004 09:52:58 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: avg() for timestamp 