Re: date_part returns different results with same interval.

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(at)wavefire(dot)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(at)postgresql(dot)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 .
/ \ -----------------------------------------------------------------