Re: Interval Rounding

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