Thread: pgsql-server: > After all that about numbering centuries and millenia

Log Message:
-----------
> 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.

Fabien Coelho

Modified Files:
--------------
    pgsql-server/src/backend/utils/adt:
        timestamp.c (r1.109 -> r1.110)

(http://developer.postgresql.org/cvsweb.cgi/pgsql-server/src/backend/utils/adt/timestamp.c.diff?r1=1.109&r2=1.110)
    pgsql-server/src/test/regress/expected:
        date.out (r1.5 -> r1.6)
        (http://developer.postgresql.org/cvsweb.cgi/pgsql-server/src/test/regress/expected/date.out.diff?r1=1.5&r2=1.6)
    pgsql-server/src/test/regress/sql:
        date.sql (r1.3 -> r1.4)
        (http://developer.postgresql.org/cvsweb.cgi/pgsql-server/src/test/regress/sql/date.sql.diff?r1=1.3&r2=1.4)