Thread: date_part returns different results with same interval.
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 .
/ \ -----------------------------------------------------------------
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)
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 . / \ -----------------------------------------------------------------
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