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
);