Bug #761: Date format ambiguity while insertion!!!!

From: pgsql-bugs(at)postgresql(dot)org
To: pgsql-bugs(at)postgresql(dot)org
Subject: Bug #761: Date format ambiguity while insertion!!!!
Date: 2002-09-09 08:48:29
Message-ID: 20020909084829.83B06475BD7@postgresql.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

Shubhankar Dasgupta (dasgupta(at)intelesoftech(dot)com) reports a bug with a severity of 1
The lower the number the more severe it is.

Short Description
Date format ambiguity while insertion!!!!

Long Description
I am using Postgres 7.2.2 on Redhat Linux 7.2

My datestyle settings have been set as "ISO US(non european)"
This would mean that my date values should get inserted in the mm-dd-yyyy format. "09-01-2002" is interpreted as Sepetember 1st, 2002 while "01-09-2002" is interpreted as January 9th, 2002.
This works perfectly most of the time except during certain cases where my month field exceeds 12.
As an example--consider "13-01-2002". As per my datesyle settings, postgres should have tried to interpret this as the 1st day of some "UNKNOWN" month of 2002 and should have thrown an error. However this date gets inserted and gets stored as 13th January, 2002 (interperted in the dd-mm-yyyy format).
Its only when both the first two fields exceed 12(something like 13-13-2002), that I am given an error "Bad date external representation '13-13-2002'. If either of the first two fields is less than or equal to 12, postgres accepts that as the month field irrespective of my datestyle settings.
This may be a flexibility feature but then this I believe is extremely dangerous as it would accept user-errors without the user knowing it.

Sample Code
ims=> insert into testdate values('1','09-01-2002');
INSERT 1590456 1
ims=> insert into testdate values('2','01-09-2002');
INSERT 1590457 1
ims=> insert into testdate values('3','13-09-2002');
INSERT 1590458 1
ims=> insert into testdate values('4','09-13-2002');
INSERT 1590459 1
ims=> insert into testdate values('5','13-13-2002');
ERROR: Bad date external representation '13-13-2002'
ims=> select * from testdate ;
dateid | mydate
--------+------------
1 | 2002-09-01
2 | 2002-01-09
3 | 2002-09-13
4 | 2002-09-13
(4 rows)

No file was uploaded with this report

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Dmitry Gertsog 2002-09-09 09:16:13 update 7.0 -> 7.2.2 problem with shared libraries?!
Previous Message Joe Conway 2002-09-09 05:31:26 Re: Schemas not available for pl/pgsql %TYPE....