Here is the code to efficiently compute an average of a timestamp column. I've only tested this on 9.1, but it will probably work on earlier versions as well. Note, you'll need to cast the column to a plain timestamp (e.g. SELECT avg(tstz_col AT TIME ZONE 'UTC') FROM mytable) in order to use it with columns of type 'timestamp with time zone'.
Author: Josh Kupershmidt
-- In order to have a reasonably efficient accumulator -- function, we need a state variable keeping a running -- total of seconds since the epoch, along with the number -- of elements processed already. CREATE TYPE ts_accum_typ AS ( running_total numeric, num_elems bigint ); -- Accumulator function. Keep a running total of the -- number of seconds since the epoch (1970-01-01), as well -- as the number of elements we have processed already. CREATE OR REPLACE FUNCTION ts_accum (existing ts_accum_typ, newval timestamp) RETURNS ts_accum_typ AS $$ DECLARE retval ts_accum_typ; BEGIN IF newval IS NULL THEN RETURN existing; END IF; IF existing IS NULL THEN retval.running_total = EXTRACT(epoch FROM newval); retval.num_elems = 1; RETURN retval; ELSE existing.running_total = existing.running_total + EXTRACT(epoch FROM newval); existing.num_elems = existing.num_elems + 1; RETURN existing; END IF; END; $$ LANGUAGE PLPGSQL IMMUTABLE; -- Final function for the timestamp 'avg' aggregate. CREATE OR REPLACE FUNCTION ts_avg (existing ts_accum_typ) RETURNS timestamp AS $$ DECLARE since_epoch numeric; BEGIN -- Handle the case when avg() is called with no rows: answer should be NULL. IF existing IS NULL THEN RETURN NULL; END IF; since_epoch = existing.running_total / existing.num_elems; RETURN to_timestamp(since_epoch); END; $$ LANGUAGE PLPGSQL IMMUTABLE; CREATE AGGREGATE avg (timestamp) ( sfunc = ts_accum, stype = ts_accum_typ, finalfunc = ts_avg );