Re: date_part returns different results with same interval. - Mailing list pgsql-bugs

Hello again,
  Should an interval not be arbitrary?  The portion of the information one
requests should not depend on the representation.  I guess what I am trying
to say is that the date_part() call is how you determine what you are
asking...  If I want to get the number of years, it shouldn't matter if my
interval is '1 year, 1 month' or '396 days', they both contain 1 year.  If
the answer changes depending on the representation of two otherwise-identical
intervals, this can introduce all kinds of errors into program logic.  It
just doesn't make a lot of sense unless I am missing something here.
 -Wade

On February 20, 2004 05:47, Bruno Wolff III wrote:
> On Thu, Feb 19, 2004 at 16:52:37 -0800,
>
>   Wade Klaver <archeron@wavefire.com> wrote:
> > Hello folks.
> >
> > I just noticed some funky behaviour on the part of date_part.  If there
> > is some reason this is correct behaviour, I wouldn't mind knowing why.
> > The problem is that date_part can return different results given to
> > essentially identical intervals.  It seems to maybe be obeying the letter
> > of the law if not the spirit? The following session from a -CURRENT build
> > demonstrates this.
>
> Intervals have two parts. One is an absolute time difference (I think
> stored in seconds), that should be used for getting days, hours, minutes
> and seconds. The other part is a difference in months that is used
> for getting months and years.
>
> Under some circumstances months get converted to 30 days each.
>
> It seems reasonable that date_part keeps these parts separate as it
> allows a way to look at each part of the interval. I don't know
> if there is another function that allows you to do that.
>
> > Thanks in advance.
> >
> > wade=# select age(now(), 'Jan 1, 2002'::date);
> >                     age
> > -------------------------------------------
> >  2 years 1 mon 18 days 16:24:54.4191970001
> > (1 row)
> >
> > wade=# select date_part( 'years', age(now(), 'Jan 1, 2002'::date));
> >  date_part
> > -----------
> >          2
> > (1 row)
> >
> > wade=# select now() - 'Jan 1, 2002'::date;
> >            ?column?
> > ------------------------------
> >  779 days 16:25:03.9250539988
> > (1 row)
> >
> > wade=# select date_part( 'years', now() - 'Jan 1, 2002'::date);
> >  date_part
> > -----------
> >          0
> > (1 row)
> >
> >
> > --
> > Wade Klaver
> > Wavefire Technologies Corporation
> > GPG Public Key at http://archeron.wavefire.com
> >
> > /"\   ASCII Ribbon Campaign  .
> > \ / - NO HTML/RTF in e-mail  .
> >  X  - NO Word docs in e-mail .
> > / \ -----------------------------------------------------------------
> >
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 2: you can get off all lists at once with the unregister command
> >     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster

--
Wade Klaver
Wavefire Technologies Corporation
GPG Public Key at http://archeron.wavefire.com

/"\   ASCII Ribbon Campaign  .
\ / - NO HTML/RTF in e-mail  .
 X  - NO Word docs in e-mail .
/ \ -----------------------------------------------------------------