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@xxxxxxxxxxxxxxxxxxxxxxx
412-422-3463 x4015
