Re: date_part returns different results with same interval.

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)