Timestamp Average

From PostgreSQL wiki

Revision as of 01:19, 13 July 2012 by Schmiddy (Talk | contribs)

(diff) ← Older revision | Latest revision (diff) | Newer revision → (diff)
Jump to: navigation, search

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