Re: Bug: century/millenium still broken - Mailing list pgsql-bugs

From Fabien COELHO
Subject Re: Bug: century/millenium still broken
Date
Msg-id Pine.LNX.4.58.0408101434310.1998@sablons.cri.ensmp.fr
Whole thread Raw
In response to Bug: century/millenium still broken  (Tom Lane <tgl@sss.pgh.pa.us>)
Responses Re: [PATCHES] Bug: century/millenium still broken  (Bruce Momjian <pgman@candle.pha.pa.us>)
Re: [PATCHES] Bug: century/millenium still broken  (Bruce Momjian <pgman@candle.pha.pa.us>)
List pgsql-bugs
Dear Tom,

> After all that about numbering centuries and millenia correctly,
> why does CVS tip still give me
>
> regression=# select extract(century from now());
>  date_part
> -----------
>         20
> (1 row)
> [ ... looks in code ... ]
>
> Apparently it's because you fixed only timestamp_part, and not
> timestamptz_part.  I'm not too sure about what timestamp_trunc or
> timestamptz_trunc should do, but they may be wrong as well.

Sigh... as usual, what is not tested does not work:-(


> Could we have a more complete patch?

Please find a submission attached. I hope it really fixes all decade,
century and millenium issues for extract and *_trunc functions on interval
and other timestamp types. If someone could check that the results
are reasonnable, it would be great.

I indeed overlooked the fact that there were two functions. The patch
fixes the code so that both variants agree.

I added comments to interval extractions, because it relies on the C
division to have a negative remainder: -7/10 = 0 and remains -7.

As for *_trunc functions, I have chosen to put the first year of the
century or millennium: -100, 1, 101... 1001 2001 etc. Indeed, I don't
think it would make sense to put 2000 (last year of the 2nd millennium)
for rounding all years of the third millenium.

I also fixed the code so that all decades last 10 years and decade 199
means the 1990's.

I have added some tests that are relevant to deal with tricky cases. The
formula may be simplified, but all these cases must pass. Please keep
them.

Have a nice day,

--
Fabien Coelho - coelho@cri.ensmp.fr

pgsql-bugs by date:

Previous
From: "PostgreSQL Bugs List"
Date:
Subject: BUG #1208: Invalid page header
Next
From: "PostgreSQL Bugs List"
Date:
Subject: BUG #1209: Problem related to installation