Forum OpenACS Q&A: Response to PostgreSQL date questions

Here's a function that computes the interval, in number of seconds, between two timestamps.

CREATE FUNCTION timebox_get_interval(timestamp, timestamp)
RETURNS integer
AS '
  DECLARE 
    trans_start ALIAS FOR $1;
    trans_end ALIAS FOR $2;
    time_diff interval;
    time_diff_text text;
    sec_str text;
    min_str text;
    hr_str  text;
    sec_interval integer;
  BEGIN
    time_diff := trans_end - trans_start;
    time_diff_text := (time_diff)::text;
    sec_str := substr(time_diff_text, 7, 2);
    min_str := substr(time_diff_text, 4, 2);
    hr_str := substr(time_diff_text, 1, 2);
    sec_interval := sec_str::integer + ((min_str::integer)*60) + ((hr_str::integer)*3600); 
    RETURN sec_interval;
  END;'
LANGUAGE 'plpgsql';