Thread: Date input changed in 7.4 ?
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
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, Pennsylvania19073
"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: >"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 ... > > >
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 ? > 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) >
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