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