Обсуждение: Interval Rounding

Hello,
  I have a table that contains a user's response and a timestamp at
which it was last updated.  I want to query for this response, and the
amount of time that has elapsed since, rounded to the nearest unit (e.g.
minutes, days, months, etc).  I am using:

SELECT NOW() - change_time
FROM ...

to get the interval, and am attempting to use either EXTRACT() or
DATE_PART() to get the appropriate value, but the interval doesn't
contain any unit higher than days.

Any thoughts on how I could accomplish this?  Thanks in advance

Mike Ginsburg
Collaborative Fusion, Inc.
mginsburg@collaborativefusion.com

On Jun 1, 2007, at 12:11 , Mike Ginsburg wrote:

> SELECT NOW() - change_time
> FROM ...
>
> to get the interval, and am attempting to use either EXTRACT() or
> DATE_PART() to get the appropriate value, but the interval doesn't
> contain any unit higher than days.

It'd be helpful to me to see the result you're getting as well as the
result you expect. For example, in v8.2.4

select current_timestamp - '2006-01-01';
          ?column?
--------------------------
516 days 11:31:23.899746
(1 row)

(CURRENT_TIMESTAMP is SQL-spec for now())

Do you mean why doesn't the second one return something like '1 year
5 months 11:31:23.899746'? I believe the reason is that timestamp
subtraction doesn't want to make assumptions as to how long a year or
a month is. The resulting interval doesn't include any information as
to how long those intervening months were, and Postgres isn't smart
enough to know what you want to do with the resulting interval.

You might want to look at age(), which does what you're expecting, I
believe.

select age(current_timestamp, '2006-01-01');
               age
-------------------------------
1 year 5 mons 12:36:39.291207
(1 row)

Hope that helps.

Michael Glaesemann
grzm seespotcode net

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?

Michael Glaesemann wrote:
>
> On Jun 1, 2007, at 12:11 , Mike Ginsburg wrote:
>
>> SELECT NOW() - change_time
>> FROM ...
>>
>> to get the interval, and am attempting to use either EXTRACT() or
>> DATE_PART() to get the appropriate value, but the interval doesn't
>> contain any unit higher than days.
>
> It'd be helpful to me to see the result you're getting as well as the
> result you expect. For example, in v8.2.4
>
> select current_timestamp - '2006-01-01';
>          ?column?
> --------------------------
> 516 days 11:31:23.899746
> (1 row)
>
> (CURRENT_TIMESTAMP is SQL-spec for now())
>
> Do you mean why doesn't the second one return something like '1 year 5
> months 11:31:23.899746'? I believe the reason is that timestamp
> subtraction doesn't want to make assumptions as to how long a year or
> a month is. The resulting interval doesn't include any information as
> to how long those intervening months were, and Postgres isn't smart
> enough to know what you want to do with the resulting interval.
>
> You might want to look at age(), which does what you're expecting, I
> believe.
>
> select age(current_timestamp, '2006-01-01');
>               age
> -------------------------------
> 1 year 5 mons 12:36:39.291207
> (1 row)
>
> Hope that helps.
>
> Michael Glaesemann
> grzm seespotcode net
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: Don't 'kill -9' the postmaster
>
>
>
>
>
>

Mike Ginsburg
Collaborative Fusion, Inc.
mginsburg@collaborativefusion.com
412-422-3463 x4015

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