On Jun 1, 2007, at 13:00 , Mike Ginsburg wrote:
age() is exactly what I needed. Now I just feel dumb for not
looking into it. As far as getting the highest unit (day, month,
year, etc) I am currently using CASES
SELECT CASE
WHEN (now() - change_time) < '1 min'::interval
THEN date_part('seconds', age(now(), change_time))
WHEN (now() - change_time) < '1 hour'::interval
THEN date_part('minutes', age(now(), change_time))
END
FROM...
Any better way to do it?
Personally I'd push the age() into a subquery so it's only called
once (though I think PostgreSQL knows it only needs to evaluate it
once) or maybe wrap the whole case statement in a function (untested):
CREATE FUNCTION approximate_age
(
p_since TIMESTAMP WITH TIME ZONE
) RETURNS DOUBLE PRECISION
IMMUTABLE
LANGUAGE PLPGSQL
AS $_$
DECLARE
v_age INTERVAL;
v_approximate_age DOUBLE PRECISION;
v_precision TEXT;
BEGIN
v_age := age(p_since);
IF v_age < INTERVAL '1 min' THEN
v_precision := 'seconds';
ELSIF v_age < INTERVAL '1 hour' THEN
v_precision := 'minutes';
-- ...
END IF;
IF v_precision IS NULL
-- catch case when no precision has been set
v_approximate_age = v_age;
ELSE
v_approximate_age := date_part(v_precision, v_age);
END IF;
RETURN v_approximate_age;
$_$;
Then just SELECT approximate_age(change_time);
Michael Glaesemann
grzm seespotcode net
