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