Re: Interval Rounding

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