I don't see this as applied yet. ---------------------------------------------------------------------------
Tom Lane wrote: > "Pelle Johansson" <[EMAIL PROTECTED]> writes: > > The age() function seem to work by first counting months until less than a > > month remains to to the second argument, then counting days left. This > > doesn't give the correct result, as shown by this example: > > > # select column1, age(column1, '2006-11-02'), date '2006-11-02' + > > age(column1, '2006-11-02') from (values ('2007-01-31'::date), > > ('2007-02-01')) as alias; > > column1 | age | ?column? > > ------------+----------------+--------------------- > > 2007-01-31 | 2 mons 29 days | 2007-01-31 00:00:00 > > 2007-02-01 | 2 mons 29 days | 2007-01-31 00:00:00 > > (2 rows) > > I took another look at this example. I believe what is actually going > wrong here is that when timestamp_age converts a month into an > equivalent number of days, it uses the number of days in the first > month of the interval it's dealing with (ie, the month containing > the earlier of the two dates). This is just wrong, because interval > addition adds months first and then days. The appropriate conversion > to use is actually the length of the next-to-last month of the interval. > > As an example, 8.2 and CVS HEAD produce > > regression=# select age('2007-03-14', '2007-02-15'); > age > --------- > 27 days > (1 row) > > which is reasonable, but > > regression=# select age('2007-04-14', '2007-02-15'); > age > --------------- > 1 mon 27 days > (1 row) > > is not so reasonable, nor is > > regression=# select age('2007-03-14', '2007-01-15'); > age > --------------- > 1 mon 30 days > (1 row) > > If we change the code to use the next-to-last month of the interval > then these two cases produce '1 mon 30 days' and '1 mon 27 days' > respectively. > > Another problem is that the code isn't doing the propagate-to-next-field > bit for negative fractional seconds. Hence it produces > > regression=# select age('2007-02-14 01:00:00', '2007-01-15 01:00:00.4'); > age > ---------------------- > 30 days -00:00:00.40 > (1 row) > > which is maybe not incorrect, but certainly fairly inconsistent with > > regression=# select age('2007-02-14 01:00:00', '2007-01-15 01:00:01'); > age > ------------------ > 29 days 23:59:59 > (1 row) > > > Hence I propose the attached patch. This does not change any existing > regression test outputs, but it does change the example given in the > documentation: age(timestamp '2001-04-10', timestamp '1957-06-13') > will now produce '43 years 9 mons 28 days' not 27 days. Which actually > is correct if you try to add back the result to timestamp '1957-06-13'. > It also appears to fix Palle's example: > > regression=# select column1, age(column1, '2006-11-02'), date '2006-11-02' + > age(column1, '2006-11-02') from (values ('2007-01-31'::date), > ('2007-02-01')) as alias; > column1 | age | ?column? > ------------+----------------+--------------------- > 2007-01-31 | 2 mons 29 days | 2007-01-31 00:00:00 > 2007-02-01 | 2 mons 30 days | 2007-02-01 00:00:00 > (2 rows) > > As I said earlier, I'm worried about changing the behavior of a function > that's been around for so long, so I'm disinclined to back-patch this. > But it seems like a reasonable change to make in 8.3. Comments? > > regards, tom lane > -- Bruce Momjian <[EMAIL PROTECTED]> http://momjian.us EnterpriseDB http://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---------------------------(end of broadcast)--------------------------- TIP 5: don't forget to increase your free space map settings