Thread: date_part returns different results with same interval.

date_part returns different results with same interval.

From
Wade Klaver
Date:
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.
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 .
/ \ -----------------------------------------------------------------

Re: date_part returns different results with same interval.

From
Bruno Wolff III
Date:
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)

Re: date_part returns different results with same interval.

From
Wade Klaver
Date:
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 .
/ \ -----------------------------------------------------------------

Re: date_part returns different results with same interval.

From
Tom Lane
Date:
Wade Klaver <archeron@wavefire.com> writes:
> 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.

I think you missed the point.  If I want to get the number of months,
it's clear what the answer is for an interval of '1 year', but not at
all clear what the answer is for an interval of '60 days'.  Months and
days don't have a fixed conversion factor.

            regards, tom lane