Thread

1. postgres and year 2000

   Massimo Dal Zotto <dz@cs.unitn.it> — 1999-01-08T15:53:55Z

   Hi,
   
   it seems that the year handling in pgsql dates is not very consistent:
   
   dz=> create table a (n int, x date);
   CREATE
   dz=> insert into a values (1,'02-02-0');
   INSERT 259246 1
   dz=> insert into a values (2,'02-02-00');
   INSERT 259247 1
   dz=> insert into a values (3,'02-02-000');
   INSERT 259248 1
   dz=> insert into a values (4,'02-02-0000');
   INSERT 259249 1
   dz=> insert into a values (5,'02-02-00000');
   INSERT 259250 1
   dz=> insert into a values (6,'02-02-1');
   INSERT 259251 1
   dz=> insert into a values (7,'02-02-01');
   INSERT 259252 1
   dz=> insert into a values (8,'02-02-001');
   INSERT 259253 1
   dz=> insert into a values (9,'02-02-0001');
   INSERT 259254 1
   dz=> insert into a values (10,'02-02-00001');
   INSERT 259255 1
   dz=> insert into a values (11,'02-02-69');
   INSERT 259256 1
   dz=> insert into a values (12,'02-02-069');
   INSERT 259257 1
   dz=> insert into a values (13,'02-02-0069');
   INSERT 259258 1
   dz=> insert into a values (14,'02-02-00069');
   INSERT 259259 1
   dz=> insert into a values (15,'02-02-71');
   INSERT 259260 1
   dz=> insert into a values (16,'02-02-071');
   INSERT 259261 1
   dz=> insert into a values (17,'02-02-0071');
   INSERT 259262 1
   dz=> insert into a values (18,'02-02-00071');
   INSERT 259263 1
   dz=> select * from a;
    n|x            
   --+-------------
    1|02-02-2000   
    2|02-02-2000   
    3|02-02-2000   
    4|02-02-0001 BC
    5|02-02-2000   
    6|02-02-2001   
    7|02-02-2001   
    8|02-02-2001   
    9|02-02-0001   
   10|02-02-2001   
   11|02-02-2069   
   12|02-02-2069   
   13|02-02-0069   
   14|02-02-2069   
   15|02-02-1971   
   16|02-02-1971   
   17|02-02-0071   
   18|02-02-1971   
   
   The problem I see is that 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.
   Only then we could declare pgsql as full Y2K compliant.
   
   -- 
   Massimo Dal Zotto
   
   +----------------------------------------------------------------------+
   |  Massimo Dal Zotto               email: dz@cs.unitn.it               |
   |  Via Marconi, 141                phone: ++39-0461534251              |
   |  38057 Pergine Valsugana (TN)      www: http://www.cs.unitn.it/~dz/  |
   |  Italy                             pgp: finger dz@tango.cs.unitn.it  |
   +----------------------------------------------------------------------+
   
   

2. Re: [HACKERS] postgres and year 2000

   Thomas Lockhart <lockhart@alumni.caltech.edu> — 1999-01-08T16:42:53Z

   > it seems that the year handling in pgsql dates is not very consistent:
   > The problem I see is that 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.
   
   I agree that there are some cases in your examples which should be
   giving a different result. Not *every* example you gave led to an
   incorrect or unpredictable result, so could you please identify those
   you feel are a problem? In glancing at the examples, the ones with zero
   value (but lots of zeros) seem to be a problem, and perhaps some or all
   of the ones with an odd number of year digits. Any others?
   
   We do need to handle two-digit years, and we currently do so using 1970
   as the break point. I've read recently that some industries are using a
   "50/50 rule" for 2 digit conversions, where 1950 is the break point.
   Don't know if we should try to use that (rather than the "Unix rule" :),
   since it really doesn't offer a magic cure for all date possibilities.
   
   > Only then we could declare pgsql as full Y2K compliant.
   
   fwiw, the date candidates which are failing are outside the range of
   normal usage or could be considered mal-formed. But I should be able to
   get a fix in for it, and can post patches. Let me know what cases you
   would like tested and fixed (but let's not bog down in discussion on
   two-digit issues).
   
                         - Tom
   
   

3. Re: [HACKERS] postgres and year 2000

   Thomas Lockhart <lockhart@alumni.caltech.edu> — 1999-01-09T18:38:18Z

   > 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

4. Re: [HACKERS] postgres and dates (year 2000? not!)

   Thomas Lockhart <lockhart@alumni.caltech.edu> — 1999-01-10T18:08:16Z

   > it seems that the year handling in pgsql dates is not very consistent:
   > The problem I see is that the same number is converted to a different 
   > year depending on the number of digits and the number itself.
   
   I've posted a patch intended for v6.4 and v6.4.2 at
     ftp://postgresql.org/pub/patches/dt.c.patch
   
   which addresses some date interpretation problems, mostly for dates
   containing leading zeros and/or an odd number of digits (I'm referring
   to this as the "Y1K problem" :). It also fixes problems with two-digit
   years for "concatenated dates" like "990110" and adds a "yydoy" 5 digit
   concatenated date, like "99010".
   
   I'll claim that none of these problems or fixes were related directly to
   a "Y2K" problem, since it didn't work in "Y1900" either :/
   
   cvs commit notes are included below. Let me know if there are any
   problems.
   
                          - Tom
   
   Be more careful to check input string lengths as well as values
    when deciding whether a field is a year field. Assume *anything* longer
    than 2 digits (if it isn't a special-case doy) is a valid year.
    This should fix the "Y1K" and "Y10K" problems pointed out by Massimo 
    recently.
   Check usage of BC to require a positive-valued year; before just used
    to flip the sign of the year without checking. This led to problems
    near year zero.
   Allow a 5 digit "concatenated date" of 2 digit year plus day of year.
   Do 2->4 digit year correction for 6 and 5 digit "concatenated dates".
    Somehow forgot this originally. Guess not many folks use it...
   
   

5. Re: [HACKERS] postgres and year 2000

   Tom Ivar Helbekkmo <tih@nhh.no> — 1999-01-11T07:01:54Z

   "Thomas G. Lockhart" <lockhart@alumni.caltech.edu> writes:
   
   > We do need to handle two-digit years, [...]
   
   Is it at all possible to get away with _not_ doing so?  It is, after
   all, incredibly stupid to use two-digit years in anything but spoken
   conversation, so in a way, I'd prefer computer systems to blankly
   refuse them.  If they're allowed at all, I'd say parse them so that a
   year specification of '99' means the actual year 99.  _Not_ 1999.
   
   Then again, I also think computer systems should refuse to accept
   non-ISO8601 date specifications, so I may be a bit too pedantic.  :-)
   
   -tih
   -- 
   Popularity is the hallmark of mediocrity.  --Niles Crane, "Frasier"
   
   

6. Re: [HACKERS] postgres and year 2000

   Marc Fournier <scrappy@hub.org> — 1999-01-11T07:26:51Z

   On 11 Jan 1999, Tom Ivar Helbekkmo wrote:
   
   > "Thomas G. Lockhart" <lockhart@alumni.caltech.edu> writes:
   > 
   > > We do need to handle two-digit years, [...]
   > 
   > Is it at all possible to get away with _not_ doing so?  It is, after
   > all, incredibly stupid to use two-digit years in anything but spoken
   > conversation, so in a way, I'd prefer computer systems to blankly
   > refuse them.  If they're allowed at all, I'd say parse them so that a
   > year specification of '99' means the actual year 99.  _Not_ 1999.
   
   Falling back to a Unix standard...type 'cal 99' and see which year you
   get :)
   
   I agree with Tom on this...if someone types a year of 99, we should
   presume that whomever entered it knew what they were entering, and/or that
   the programmer of the interface had enough sense to program checks into
   it...
   
   
   Marc G. Fournier                                
   Systems Administrator @ hub.org 
   primary: scrappy@hub.org           secondary: scrappy@{freebsd|postgresql}.org 
   
   
   

7. Re: [HACKERS] postgres and year 2000

   Thomas Lockhart <lockhart@alumni.caltech.edu> — 1999-01-12T02:48:23Z

   > > "Thomas G. Lockhart" <lockhart@alumni.caltech.edu> writes:
   > > > We do need to handle two-digit years, [...]
   > > Is it at all possible to get away with _not_ doing so?  It is, after
   > > all, incredibly stupid to use two-digit years in anything but spoken
   > > conversation, so in a way, I'd prefer computer systems to blankly
   > > refuse them.  If they're allowed at all, I'd say parse them so that 
   > > a year specification of '99' means the actual year 99.  _Not_ 1999.
   > Falling back to a Unix standard...type 'cal 99' and see which year you
   > get :)
   > I agree with Tom on this...if someone types a year of 99, we should
   > presume that whomever entered it knew what they were entering, and/or 
   > that the programmer of the interface had enough sense to program 
   > checks into it...
   
   Well, we're all Toms here, but I'll assume you are trying to agree with
   Tom H.
   
   You may think that ISO-8601 is the way to go, and I may think that
   ISO-8601 is the way to go, but it isn't yet a universal usage, so why
   should we presume that it is? That timezone stuff is pretty annoying and
   confusing too, so perhaps we should revert Postgres back to its pre-v3
   GMT-only date support? :)
   
   If we don't accept a reasonably wide range of common date and time
   specifications, then each app will have to, or may have to, do that.
   istm that it is *much* easier to write an app which restricts data entry
   to a particular style, which is then forwarded to our backend, than to
   accept *only* a particular style in the backend and expect every client
   app, in every supported language, on every platform, to learn how to
   accept general date values and then convert them to a single specific
   one.
   
   I should also point out that in a recent review of our database the
   date/time I/O was mentioned (with 2 or 3 other features) as being
   particularly noteworthy. So *someone* thought it was a nice feature :)
   
   I suppose we could consider a compile-time or run-time option to
   constrain dates to a single style. Seems a bit of overkill, and
   personally I'd like to work on outer joins, but...
   
                       - Tom