Date input changed in 7.4 ?

Mendola Gaetano wrote:

Hi all,
I noticed that some date are not anymore accepted:

Postgres 7.3.3:

test=# select '18/03/71'::date;
date
------------
1971-03-18
(1 row)

Postgres 7.4beta1:

test=# select '18/03/71'::date;
ERROR: invalid input syntax for date: "18/03/71"

is this the indendeed behaviour ?

Yes, we now honor datestyle to determine how to deal with dates where
the year is at the end. You can set your date style to 'euopean' or the
new 'DMY' value to allow this.

This highlights the fact I need to get the compatibility section written
for the history file soon.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

"Mendola Gaetano" <mendola@bigfoot.com> writes:

I noticed that some date are not anymore accepted:
test=# select '18/03/71'::date;
ERROR: invalid input syntax for date: "18/03/71"
is this the indendeed behaviour ?

If it does not match your DateStyle setting, then yes.

regression=# select '18/03/71'::date;
ERROR: invalid input syntax for date: "18/03/71"
regression=# show DateStyle ;
DateStyle
-----------
ISO, MDY
(1 row)

regression=# set datestyle = dmy;
SET
regression=# select '18/03/71'::date;
date
------------
1971-03-18
(1 row)

regards, tom lane

"Bruce Momjian" <pgman@candle.pha.pa.us> wrote:

Mendola Gaetano wrote:

Hi all,
I noticed that some date are not anymore accepted:

Postgres 7.3.3:

test=# select '18/03/71'::date;
date
------------
1971-03-18
(1 row)

Postgres 7.4beta1:

test=# select '18/03/71'::date;
ERROR: invalid input syntax for date: "18/03/71"

is this the indendeed behaviour ?

Yes, we now honor datestyle to determine how to deal with dates where
the year is at the end. You can set your date style to 'euopean' or the
new 'DMY' value to allow this.

This highlights the fact I need to get the compatibility section written
for the history file soon.

May I also suggest to change in date.c the 3 generic error:

"invalid input syntax for date: "

with more comprensive messages ?

Regards
Gaetano Mendola

"Tom Lane" <tgl@sss.pgh.pa.us> wrote:

"Mendola Gaetano" <mendola@bigfoot.com> writes:

I noticed that some date are not anymore accepted:
test=# select '18/03/71'::date;
ERROR: invalid input syntax for date: "18/03/71"
is this the indendeed behaviour ?

If it does not match your DateStyle setting, then yes.

regression=# select '18/03/71'::date;
ERROR: invalid input syntax for date: "18/03/71"
regression=# show DateStyle ;
DateStyle
-----------
ISO, MDY
(1 row)

regression=# set datestyle = dmy;
SET
regression=# select '18/03/71'::date;
date
------------
1971-03-18
(1 row)

Great.

How I already suggest to Bruce I think is better give an hint
on the error reported just to avoid to be overloaded of
emails like mine.

My cent.

Regards
Gaetano Mendola

"Mendola Gaetano" <mendola@bigfoot.com> writes:

May I also suggest to change in date.c the 3 generic error:
"invalid input syntax for date: "
with more comprensive messages ?

That's easier said than done; there are enough different valid syntaxes
that it's not always obvious what the user's intent was. (Indeed, the
reason for this change was exactly that the code was guessing wrong too
much of the time.) See also the thread at
http://archives.postgresql.org/pgsql-bugs/2003-08/msg00113.php

I'm currently thinking about reporting "invalid syntax" if
ParseDateTime() fails and "invalid field value" if the various Decode()
routines fail, but I'm quite unsure that that will be helpful ...

regards, tom lane

How about reporting the current DateStyle in such messages? Then it
should be clear if the date parse failed because of a mismatch.

andrew

Tom Lane wrote:

On Tue, Aug 26, 2003 at 12:31:57PM -0400, Tom Lane wrote:

I'm currently thinking about reporting "invalid syntax" if
ParseDateTime() fails and "invalid field value" if the various Decode()
routines fail, but I'm quite unsure that that will be helpful ...

Maybe the HINT field could say something about the DateStyle setting...
(something rather verbose, possible including the current DateStyle
value!)

--
Alvaro Herrera (<alvherre[@]dcc.uchile.cl>)
"I call it GNU/Linux. Except the GNU/ is silent." (Ben Reiter)

Tom Lane wrote:

"Mendola Gaetano" <mendola@bigfoot.com> writes:

I noticed that some date are not anymore accepted:
test=# select '18/03/71'::date;
ERROR: invalid input syntax for date: "18/03/71"
is this the indendeed behaviour ?

If it does not match your DateStyle setting, then yes.

Umm...I hope this is controllable with a GUC variable then.

There are some cases where it's extremely useful for PostgreSQL to
accept dates of any format it knows about (ambiguities should be
resolved either by looking at the current DateStyle or, failing that, by
applying the recognition in a well-defined order). In my case I can
probably code around it but it does require some extra effort. But I
can easily imagine situations in which that wouldn't be an option.

Whatever happened to "be liberal in what you accept and conservative in
what you send"? :-)

--
Kevin Brown kevin@sysexperts.com

Kevin Brown <kevin@sysexperts.com> writes:

Tom Lane wrote:

If it does not match your DateStyle setting, then yes.

Umm...I hope this is controllable with a GUC variable then.

I was against that change myself, but I lost the argument.

regards, tom lane

You can always just go 'set datestyle...' before doing your insert I guess.

Chris

----- Original Message -----
From: "Tom Lane" <tgl@sss.pgh.pa.us>
To: "Kevin Brown" <kevin@sysexperts.com>
Cc: <pgsql-hackers@postgresql.org>
Sent: Thursday, August 28, 2003 11:36 AM
Subject: Re: [HACKERS] Date input changed in 7.4 ?

On Wed, 27 Aug 2003, Kevin Brown wrote:

There are some cases where it's extremely useful for PostgreSQL to
accept dates of any format it knows about (ambiguities should be
resolved either by looking at the current DateStyle or, failing that, by
applying the recognition in a well-defined order

And the argument bhen this was that it only leads to wrong data. As I see
it, the only time you have dates in different styles is when you get it
from a human entering dates. Then he/she will enter 01/30/03 and it is
interpreted as 2003 January 30, he/she feels happy and enters another date
in january, say 01/10/03 and now maybe it is interpreted as 2003 October
1. Of course that error is not noticed since it worked the previous time..

Even when the dates are generated by a program one should set the
datertyle to match what the program outputs, otherwise one are in trouble
anyway. If the program generate 01/10/03 pg must know what it means and
can not just guess.

I think it is a great change. Having the database guess what you mean
should at least not be the default. Having GuessDates as a variable could
be useful and I thought that was the decision back then (what the
variable was called I don't remember).

--
/Dennis

On Thursday, Aug 28, 2003, at 00:07 America/Chicago, Dennis Björklund
wrote:

On Wed, 27 Aug 2003, Kevin Brown wrote:

There are some cases where it's extremely useful for PostgreSQL to
accept dates of any format it knows about (ambiguities should be
resolved either by looking at the current DateStyle or, failing that,
by
applying the recognition in a well-defined order

And the argument bhen this was that it only leads to wrong data. As I
see
it, the only time you have dates in different styles is when you get it
from a human entering dates. Then he/she will enter 01/30/03 and it is
interpreted as 2003 January 30, he/she feels happy and enters another
date
in january, say 01/10/03 and now maybe it is interpreted as 2003
October
1. Of course that error is not noticed since it worked the previous
time..

Yes, yes, yes. I've run into exactly that problem when scripting MS
Outlook. All the dates on the twelfth of the month or earlier had the
month and day transposed. It never threw an error. I checked the stuff
with my own birthday (the 26th of April) so I didn't notice the problem
until a user pointed it out. The moral of the story is that an error is
much better than a guess. (Alternate moral: don't be like Microsoft.)

Thanks,
Scott Lamb