Re: avg() for timestamp

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

Neil Conway <neilc(at)samurai(dot)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

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

Bruno Wolff III <bruno(at)wolff(dot)to> writes:
> Neil Conway <neilc(at)samurai(dot)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