Re: Comments on earlier age() post.

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: "Mitch Vincent" <mitch(at)venux(dot)net>
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: Comments on earlier age() post.
Date: 2000-10-12 06:12:35
Message-ID: 25350.971331155@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

"Mitch Vincent" <mitch(at)venux(dot)net> writes:
> hhs=# SELECT age('Sun Dec 03 08:00:00 2000 EST','Tue Oct 10 08:00:00 2000
> EDT') as esec;
> esec
> ------------------------
> @ 1 mon 24 days 1 hour
> (1 row)

> Ok, but if I turn right around and add that value back , I get :

> hhs=# SELECT ('Tue Oct 10 08:00:00 2000 EDT'::timestamp + '1 mon 24 days 1
> hour'::interval);
> ?column?
> ------------------------------
> Mon Dec 04 08:00:00 2000 EST
> (1 row)

This is more Thomas' bailiwick than mine, but it seems to me that these
operations are inherently rather ill-defined. Consider: counting
forward from Oct 10 to Dec 3, one would naturally call the interval
"1 month + 23 days" (1 month takes you to Nov 10, from which it's
23 days to Dec 3, no?). But counting backwards from Dec 3 to Oct 10
looks like "1 month + 22 days" (1 month takes you to Nov 3, from which
it's 22 days back to Oct 12). The trouble is that Oct and Nov have
different numbers of days, so you get different answers depending on
what your referent for "1 month" is.

There may indeed be a bug here --- it bothers me that counting on my
fingers gives 22/23 days where the system says 23/24. But I'm not
sure there's anything wrong with the fact that (A-B)+B != A, given
the way type interval is defined.

Maybe we need to offer a different kind of interval that avoids the
symbolic "month" rigmarole and just counts honest-to-god seconds.

regards, tom lane

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Matthew N. Dodd 2000-10-12 06:37:59 Re: Re: [HACKERS] My new job
Previous Message Tom Lane 2000-10-12 05:49:19 Re: Index on substring?