Re: avg() for timestamp

On Sat, Mar 06, 2004 at 21:50:52 -0500,
Neil Conway <neilc(at)samurai(dot)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
);