Thread: Bug: century/millenium still broken
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.
Could we have a more complete patch?
regards, tom lane
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
Your patch has been added to the PostgreSQL unapplied patches list at:
http://momjian.postgresql.org/cgi-bin/pgpatches
It will be applied as soon as one of the PostgreSQL committers reviews
and approves it.
---------------------------------------------------------------------------
Fabien COELHO wrote:
>
> 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
Content-Description:
[ Attachment, skipping... ]
>
> ---------------------------(end of broadcast)---------------------------
> TIP 8: explain analyze is your friend
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073
Patch applied. Thanks. --------------------------------------------------------------------------- Fabien COELHO wrote: > > 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 Content-Description: [ Attachment, skipping... ] > > ---------------------------(end of broadcast)--------------------------- > TIP 8: explain analyze is your friend -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania 19073