Timestamp Average

Snippets

Timestamp Average

Works with PostgreSQL

9.1

Written in

PL/pgSQL

Depends on

Nothing

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