Thread: 7.1 euro-style dates insert error
7.0.x okay, 7.1 incorrect (CVS from 24th April):
create table test(aaa date);
insert into test(aaa) values ('23.10.1997');
insert into test(aaa) values ('13.10.1997');
insert into test(aaa) values ('2.10.1997');
select * from test;
gives:
a
--------
1997-10-23
1997-10-13
1997-2-10
Automatically thinks that the last value is a US style date.
Date style is set to EURO, but I assume this has no affect on the date
parsing at insert time.
If the dates are entered as 'ccyy.mm.dd' it is okay - unfortunately all my
dates are in the format 'dd.mm.ccyy'.
Is this a bug or a user error?
Thanks,
Chris
> Automatically thinks that the last value is a US style date.
> Date style is set to EURO, but I assume this has no affect on the date
> parsing at insert time.
Yes it does, for ambiguous cases such as yours.
> If the dates are entered as 'ccyy.mm.dd' it is okay - unfortunately all my
> dates are in the format 'dd.mm.ccyy'.
> Is this a bug or a user error?
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'"...
- Thomas
> 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?
> 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
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
Inserting '19.13.2':
gives '2013-02-19' (dd.yy.mm ??? ) - not exactly what I hoped
:)
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?
Chris
----- Original Message -----
From: "Thomas Lockhart" <lockhart@alumni.caltech.edu>
To: "Chris Storah" <cstorah@e-mis.com>
Cc: <pgsql-bugs@postgresql.org>
Sent: Friday, April 27, 2001 5:04 PM
Subject: Re: 7.1 euro-style dates insert error
> > Automatically thinks that the last value is a US style date.
> > Date style is set to EURO, but I assume this has no affect on the date
> > parsing at insert time.
>
> Yes it does, for ambiguous cases such as yours.
>
> > If the dates are entered as 'ccyy.mm.dd' it is okay - unfortunately
> all my
> > dates are in the format 'dd.mm.ccyy'.
> > Is this a bug or a user error?
>
> 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'"...
>
> - Thomas
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
> > 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
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