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
