Re: [HACKERS] postgres and year 2000

> it seems that the year handling in pgsql dates is not very consistent:
> the same number is converted to a different year
> depending on the number of digits and the number itself.
> I think that this kind of things are the most likely sources of Y2K
> troubles. A more consistent approach would be to treat the year
> literally and let any smart hack with dates entirely to the user under
> his responsability.

OK, here is a patch which tightens up the date/time interpretation of
random input :)

For numeric fields, anything greater than two digits in length is
required to be either a "concatenated date/time" or an explicit date.
Also, now *only* two digit years are converted to a year in a nearby
century.

> Only then we could declare pgsql as full Y2K compliant.

I agree that the interpretation of 3 and 5 digit years was not right for
years with leading zeros. This patch should fix that.

"Y2K compliant" means to me that the behavior of well-formed dates must
be predictable and understandable. Dates *intended* to be in the 19th or
20th century should end up being interpreted that way.

The Postgres date/time types allow year ranges from (roughly) Julian Day
zero, which is Nov 23, 4714 BC (I wrote it as -4713 in my notes, so I
may be off by a year), to far into the future. So if you enter a three
digit year, that's what you are going to get (or will, with this patch).
I don't see that as causing Y2K trouble; you could just as easily call
it Y1K trouble :)

Anyway, there is a tradeoff between flexibility in date specification
and catching unintended typos. Dan suggested that all dates be required
to have 4 digit years, which may be overly harsh for some users and
developers. Should we have a way to specify the range of valid dates and
times so databases installed for specific applications can have more
restrictive constraints on input? I already parameterized the year range
check with a #define. Perhaps we should have it be able to be overridden
by ./configure or by a Makefile.custom??

A patch is enclosed which fixes all of the problems I can see in your
examples. It basically does no century adjustments unless the year is
exactly two digits, and interprets anything greater than two digits as
an explicit year (or a concatenated date if 8 or 6 or 5 digits). It adds
"two digit year" adjustments to concatentated dates, which I had
apparently omitted. It also does more rigorous checking on the usage of
"BC", so that if you specify a negative or zero year along with BC it
will complain (before, it just flipped the sign on the year).

Please install and let me know what you think:
cd src/backend/utils/adt
patch < dt.c.patch
cd ../../..
make install

I will apply this to the development cvs tree sometime soon, unless
folks find problems or need something different.

Massimo, would you be interested in adding some of your test cases to
the datetime or horology regression test? Send me patches and I'll add
them in...

- Tom