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 .
/ \ -----------------------------------------------------------------


---------------------------(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)

On Thu, Feb 19, 2004 at 16:52:37 -0800,
Wade Klaver <archeron (AT) wavefire (DOT) com> wrote:
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.

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

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:
--
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 1: subscribe and unsubscribe commands go to majordomo (AT) postgresql (DOT) org

Wade Klaver <archeron (AT) wavefire (DOT) com> writes:
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

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster