Re: 7.1 euro-style dates insert error - Mailing list pgsql-bugs

I am altering the source to ISO dates ... makes it easier as I am using
'copy from'
to do the bulk uploads :)

> The algorithm for interpreting dates is in an appendix in the User's
> Guide. Does this behavior match your reading of that writeup?
Yes, that's okay - I just had a one-off case that confused me...ISO dates
solve
the problem though.

Thanks for the help,
Chris

----- Original Message -----
From: "Thomas Lockhart" <lockhart@alumni.caltech.edu>
To: "Chris Storah" <cstorah@emis-support.demon.co.uk>
Cc: <pgsql-bugs@postgresql.org>
Sent: Thursday, May 03, 2001 6:28 AM
Subject: Re: 7.1 euro-style dates insert error


> > > Yes it does, for ambiguous cases such as yours.
> > Which means that independent of the date style, it should give a date
> error
> > either way?
>
> No, it means that for ambiguous cases (e.g. '2-10-1997') it will assume
> European or US conventions were used. It will rarely reject a date on
> grounds of ambiguity, since common usage in many countries is guaranteed
> to be ambiguous. That is why ISO and four digit years are to be
> preferred.
>
> > > I'm willing to bet that the date style is *not* set to "European".
> > > Please demonstrate with a "show datestyle" and "select date
> > > '2.10.1997'"...
> > NOTICE: DateStyle is ISO with European conventions.
> > ?column?
> > -----------
> > 1997-10-02
>
> OK, so this is a correct result...
>
> > Seems to be a problem with inserting reversed dates (Eg. 1997.13.2)
> and
> > invalid dates...
> > Inserting 10.13.1997:
> >     gives 'Bad external date representation 10.13.1997'        -
> correct
>
> Hmm, I would have thought that this would be interpreted as mm.dd.yyyy
> for sure, but instead it is enforcing the "european ordering" of the
> fields. If you switch to "US" style, the date is accepted. That is OK I
> think...
>
> > Inserting '19.13.2':
> >     gives '2013-02-19'    (dd.yy.mm ??? )
>
> What would you want this interpreted as? dd.mm.y? Postgres allows years
> back to 4213BC, so a one digit year might be accepted indicating a time
> two millennia ago.
>
> The algorithm for interpreting dates is in an appendix in the User's
> Guide. Does this behavior match your reading of that writeup? Not that
> this would make it acceptable, but at least it would be working as
> advertised ;)
>
> In this case, it seems to give up right away on an ISO date since it has
> only a two digit leading field. It tries that as a day, since it can not
> possibly be a month (too big). The next field then gets picked up as the
> year, since it cannot possibly be a month (too big). Then the last field
> is picked up as a month, since that is the only thing left.
>
> > Unfortunately I am inserting 20,000 dates into a table, so it is not a
> one
> > off case.
> > Is there any way to enforce specific date formats without the parser
> > calculating the 'best-fit' case?
> > > > If the dates are entered as 'ccyy.mm.dd' it is okay -
> unfortunately
> > > > all my dates are in the format 'dd.mm.ccyy'.
>
> You had indicated that all of your dates were in a specific format with
> four digit years. Are you saying now that they should be, but that some
> of the inputs are invalid? Or are they a mix of every possibility, and
> you want to reject those with some properties but not others?
>
> If so, you might try using to_date() to enforce a specific input format.
> You might find it easier to ingest these into a text column first, then
> manipulate from there (for example, you could prepend the century
> digits). But what do you want to do with the invalid entries? Is it OK
> to ignore them??
>
>                            - Thomas
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://www.postgresql.org/search.mpl