Thread: age() function?

Hi, I see followings in current:

create table t2 (t date primary key)
[insert some data into t2]select *,age(t) from t2 where age(t) = '9 mons 30 days'::interval;    t      |      age

------------+----------------2001-03-18 | 10 mons2001-03-19 | 9 mons 30 days
(2 rows)

How come 10 mons == 9 mons 30 days?
--
Tatsuo Ishii

Tatsuo Ishii <t-ishii@sra.co.jp> writes:
> How come 10 mons == 9 mons 30 days?

Because for purposes of comparisons, type interval assumes 1 month
== 30 days (cf. interval_cmp_internal).  Pretty grotty, I agree,
but it's not easy to see how to do better.
        regards, tom lane

> > How come 10 mons == 9 mons 30 days?
> 
> Because for purposes of comparisons, type interval assumes 1 month
> == 30 days (cf. interval_cmp_internal).  Pretty grotty, I agree,
> but it's not easy to see how to do better.

Oh I see. Probably that's the reason why the standard does not allow
month-and-day-mixed interval.
--
Tatsuo Ishii

Tom Lane wrote:
> Tatsuo Ishii <t-ishii@sra.co.jp> writes:
> > How come 10 mons == 9 mons 30 days?
> 
> Because for purposes of comparisons, type interval assumes 1 month
> == 30 days (cf. interval_cmp_internal).  Pretty grotty, I agree,
> but it's not easy to see how to do better.

I guess if we knew the month we could do better.  :-)

--  Bruce Momjian                        |  http://candle.pha.pa.us pgman@candle.pha.pa.us               |  (610)
853-3000+  If your life is a hard drive,     |  830 Blythe Avenue +  Christ can be your backup.        |  Drexel Hill,
Pennsylvania19026
 

> > > How come 10 mons == 9 mons 30 days?
> > Because for purposes of comparisons, type interval assumes 1 month
> > == 30 days (cf. interval_cmp_internal).  Pretty grotty, I agree,
> > but it's not easy to see how to do better.
> Oh I see. Probably that's the reason why the standard does not allow
> month-and-day-mixed interval.

Right. I have it fall back to a fixed 30 days. Seems better than
disallowing it altogether...
                - Thomas