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 ?

Regards
Gaetano








Tue, 26 Aug 2003 17:38:48 +0200
"Mendola Gaetano" <mendola@bigfoot.com>
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

Tue, 26 Aug 2003 11:44:09 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: Date input changed in 7.4 ?
"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

Tue, 26 Aug 2003 11:48:35 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: Date input changed in 7.4 ? 
"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





Tue, 26 Aug 2003 17:53:25 +0200
"Mendola Gaetano" <mendola@bigfoot.com>
Re: Date input changed in 7.4 ?
"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










Tue, 26 Aug 2003 17:58:17 +0200
"Mendola Gaetano" <mendola@bigfoot.com>
Re: Date input changed in 7.4 ?
"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

Tue, 26 Aug 2003 12:31:57 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: Date input changed in 7.4 ? 

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:

>"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 ...
>
>  
>



Tue, 26 Aug 2003 12:52:07 -0400
Andrew Dunstan <andrew@dunslane.net>
Re: Date input changed in 7.4 ?
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)

Tue, 26 Aug 2003 14:05:18 -0400
Alvaro Herrera Munoz <alvherre@dcc.uchile.cl>
Re: Date input changed in 7.4 ?
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

Wed, 27 Aug 2003 01:04:19 -0700
Kevin Brown <kevin@sysexperts.com>
Re: Date input changed in 7.4 ?
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

Wed, 27 Aug 2003 23:36:03 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: Date input changed in 7.4 ? 
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 ? 


> 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
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
> 

Thu, 28 Aug 2003 11:47:34 +0800
"Christopher Kings-Lynne" <chriskl@familyhealth.com.au>
Re: 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


Thu, 28 Aug 2003 07:07:14 +0200 (CEST)
=?ISO-8859-1?Q?Dennis_Bj=F6rklund?= <db@zigo.dhs.org>
Re: Date input changed in 7.4 ?
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

Sat, 30 Aug 2003 16:34:53 -0500
Scott Lamb <slamb@slamb.org>
Re: Date input changed in 7.4 ?